Run Live Demo

Finding SQL Server CPU problems with DMV queries

January 16, 2018 |

There are numerous native windows utilities 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

  • Identify and fix SQL Server CPU issues without bringing the SQL production environment down.
  • No crashing allowed – Resolving database performance and CPU issues can be likened to repairing a jet aircraft while it is still flying.

 

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

  1. Most queries return information quickly and with minimal overhead.
  2. The query structures evolve with each SQL Server release with both new queries as well as changes to existing queries (i.e. new columns) added.
  3. Queries return configuration detail (i.e. OS and SQL version). The information is not only helpful in terms of demographics, but is also extremely useful when used as part of conditional scripting.
  4. Queries return Windows performance metrics as well detail about the inner workings of SQL Server. This data will show improper database tuning as well as poor server performance.

 

 

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

  1.  % Processor Time > 80%  —  SQL Server CPU is under stress
  2.  Processor Queue Length > 2  —   Processes are waiting for CPU cycles

 

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.

TOP 5O SQL QUERIES HIGH CPU.png
(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

  1. Target queries that are singularly consuming large amounts of CPU 
  2. Identify queries that are cumulatively consuming CPUQuery Average CPU Time = (total_work_time / execution_count)

     

 

Longitude leveraging DMVs to alert

Longitude collects, evaluates, and archives SQL performance data using a number of methods, among them DMVs.

 

 Longitude alert threshold for High CPU Queuries
(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.

 

Longitude - alerting on High CPU SQL Queries
(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.

Try Longitude Live Online Demo!

Access our online demo environment, see how to set up your SQL Server monitoring, view dashboards, problem events, reports and alerts.  Please log in using the credentials below:

  • Username:   demo
  • Password:    longitude

 

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.

  1. 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.
  2. 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
  3. 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

 

DMV query that extracts performance counters
(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

 

DMV query identifying query plans with excessive compiles
(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

 

 

Optimize SQL Server Performance

Learn how an improved monitoring strategy can help optimize database performance.

Reduce the amount of dedicated resources needed for database monitoring and management.