There are numerous native windows utilitites that IT can avail itself to diagnose SQL Server CPU performance problems. The utility IT chooses to use will largely depend on the level of SQL Server expertise, the nature of the problem, and the time available to build out a solution. One of the more comprehensive utilities is Dynamic Management Views “DMVs”.
Competency with DMVs can go a long way towards minimizing the number and duration of SQL Server performance issues that an IT organization has to deal with. Ultimately it is about timely problem resolution and reducing organizational friction.
- The IT Infrastructure folks will deny the problem is on their end claiming the network and server infrastructure hasn’t changed or that they'll claim the existing configuration has more capacity than necessary.
- The DBAs will feel the performance problem is being put unfairly on their lap because it is easier for IT to assume that the either DBAs did something or that there is a database tuning issue.
- Management does not care whose problem it is, they just want it fixed.
“DMVs” are query structures built into SQL Server that deliver details about server and database health/performance. DMVs provide a common mechanism to extract “all things SQL” as well as Windows OS performance data. There are multiple DMV categories that return configuration information and performance data.
DMVs are a powerful and flexible, however leveraging them does require bit of homework. To take full advantage of the construct you may want to investigate some of the books devoted entirely to the subject.
Virtualized SQL Servers are usually provisioned with plenty of resources. Assuming there hasn't been a significant change in workload most CPU related SQL issues are the result of poorly optimized queries.
Troubleshooting first starts with confirming that the SQL server's CPU usage is approaching a saturation point - the simplest mechanism is using Perfmon and looking at % Processor Time and Processor Queue Length .
|Criteria for CPU being at capacity
The DMV query in Figure 1 is pulling detail about the cached query plans from the SQL Server using the sys.dm_exec_query_stats DMV. The DMV contains quite a bit of information about the query plan, however for each query plan we are specifically interested in:
- CPU usage (total_work_time)
- number of times the query has executed - (execution_count)
- sql_handle which is needed to return the SQL text of the query plan from the sys.dm_exec_sql_text
- Lastly sorting what is returned based on total_work_time of the query.
Longitude is alerting based on the calculation of Query Average CPU Time. The Longitude configuration above is set to monitor two SQL instances and to alert IT staff if any queries on node Slovakia’s instance exceed an average of 1 second of CPU and on node Romania's instance 3 seconds.
- SQL Server Batch Requests represent the number of SQL Statements that are being executed per second. The goal is to achieve the greatest number of batch requests while keeping server resource utilization in check.
You’ll want to baseline this metric to determine what is optimal for your installation. There is no "correct" answer as a number of factors come into play including the available resources on the server as well as the speed of the disk subsystem.
- SQL Server Compilations indicate how often SQL has to compile incoming T-SQL requests, this occurs when the SQL Server has to build an execution plan for first time queries. Compiling is a CPU intensive process. Once compiled SQL caches the execution plan. If the SQL can't keep the plan in memory then it will need to compile again.
SQL Server Compilation should be about 10% of the SQL Server Batch Requests. If it is too high, it could mean too many ad-hoc queries
- SQL Server Re-compilation is usually compilation recurring because of changes to the database structure. A new execution plan has to be created to take advantage of new or different constructs.
SQL Server Re-compilations should be less than 10% of SQL Server Compilations
Keep in mind these counter values are cumulative based on SQL Server startup. Should you decide to script this you will need to compare the difference between the 2 consecutive collections and then divide by the the collection interval
This query returns query plans that are recompiling, the plan_generation_num is a sequence number distinguishes between instances of plans after a recompile. These are likely to be queries that require the most attention
Dynamic Management Views allow for quick access to the internals for SQL Server. They can be an invaluable tool provided that you have a good understanding of SQL Server internals and the resources to research and build out a set of relevant DMVs.
|sys.dm_exec_connections||Returns information about the connections established to the instance|
|sys.dm_exec_query_stats||Returns aggregate performance statistics for cached query plans in SQL Server|
|sys.dm_exec_requests||Returns the requests currenty executing and their status|
|sys.dm_exec_sessions||Returns information about all active user connections and internal tasks|
|sys.dm_exec_sql_text||Returns SQL batch that are being executed|
|sys.dm_os_schedulers||Returns scheduler information (identify runaway tasks)|
|sys.dm_os_threads||Returns detail about SQL Server operating threads running under the SQL Server process|
|sys.dm_os_waiting_tasks||Returns information about the wait queue of tasks that are waiting on some resource|
|sys.dm_os_workers||Returns how long a worker has been running (i.e. SUSPENDED or RUNNABLE state)|
|sys.dm_os_tasks||Returns information for each active task|
Want learn more?
Learn how an improved monitoring strategy can help optimize database performance and reduce the amount of dedicated resources needed for database monitoring and management