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”.
Avoid the finger pointing!
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.
Friction can occur between IT and end-users. Depending on the severity of the problem, friction can also occur within IT as well, especially when there is finger pointing:
- 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.
Ultimately it is about keeping the friction to a minimum. This is complicated by the fact that patience wears thin quickly when database servers aren’t performing properly.
|
The challenge - quick resolution and no crashing
|
Dynamic Management Views
“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.
About Dynamic Management View Queries
|
Troubleshooting High CPU
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
|
Using DMVs to identify queries using too much CPU
The quickest way to identify poorly written queries is to first go after the low hanging fruit first by targeting the queries that are using the most CPU.
 |
| (Figure 1) Show Top 50 SQL Queries in sorted order by CPU usage |
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.
|
Identifying high CPU queries
|
Longitude leveraging DMVs to alert
Longitude collects, evaluates, and archives SQL performance data using a number of methods, among them DMVs.
 |
| (Figure 2) Longitude - setting threshold for High CPU SQL Queries |
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.
 |
| (Figure 3) Longitude - alerting on High CPU SQL Queries |
Here we see Longitude alerting on query clearly using excessive CPU, using the same data from Figure 1. The query consumed over an hour of CPU time.
Using DMVs for Compilation and CPU Problems
When SQL Server experiences excessive CPU and processor queue length issues another possible cause could be excessive Compilations/sec and Re-Compilations/sec.
- 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
 |
| (Figure 4) DMV query that extracts performance counters |
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
If you prefer (and it is easier) you can access directly via Perfmon
SQL Server: SQL Statistics: Batch Requests/sec
SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
 |
| (Figure 4) DMV query identifying query plans with excessive compiles |
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
This Microsoft Technet article is a good place to learn more about compilation and re-compilation
Summary
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.
Below is a list of some of the DMVs that are quite helpful in diagnosing CPU problems on SQL Servers
| 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
