SQL Server Performance Baselining Reports10-09-2019 0 Comment(s)
- Performance is relative – DBAs, Consultants and Support team often get called in a situation when the business application running on SQL Server is running slow. Before troubleshooting, one of the first question to ask is how slow is slow and how fast is fast?. In majority of the situations, there are changes in the workload, application code which lead to changes in performance but one can prove that something is changed only if the previous state of the SQL Server was captured. In such situations, performance baselining can assist you in learning from the historical data and trends to detect the anomalies or changes on the pattern on the server.
- Capacity Planning – As DBAs managing large deployment and mission critical instances of SQL Server, it is important to proactively keep an eye on resource utilization (CPU, Memory, IO and storage) and workload trend over a period, to forecast and plan for more capacity if the workload trend or resource utilization is changing. To plan for capacity, performance baselining reports is the key to perform historical analysis of data and predict capacity required for future.
- Consolidation Planning – As shadow IT and business applications running SQL Server grows in the enterprise, companies can save cost by consolidating some of their database under single instance by efficiently utilizing their hardware and resources. To plan and understand the resource utilization of SQL Server database, again performance baselining is required.
- Virtualization\Migration to Azure – Most enterprises today are looking toward to reduce their IT capital and operational expenses and overheads. When migrating to cloud, it is important to identify their VM size or performance tier to run your SQL Server databases which is easy when you have performance baselines established.
In SQL Server, at a very high level, we have 3 types of performance monitoring data available for us to capture the state of SQL Server
- DMVs (dynamic management views)
In SQL Server, one needs to capture at least following details at minimum over time to successfully establish comprehensive performance baseline for SQL Server instance.
No Comments to Show