By: Tibor Nagy | Comments (5) | Related: 1 | 2 | 3 | 4 | > Performance Tuning
Problem
We experience regular slowdowns on our MS SQL database. After analyzing the memory usage we would like to continue the root cause investigation by examining CPU bottlenecks. What is your recommendation to recognize CPU related bottlenecks in SQL Server?
Solution
There are many reasons for CPU related performance problems on MS SQL Server. The obvious reason for CPU bottlenecks is insufficient hardware resources. However, CPU utilization can usually be reduced by configuration changes and query tuning so think before you rush out to buy faster or more processors. In this tip I will provide you some ideas how to identify CPU related bottlenecks using the built-in tools.
Performance Monitor
You can use Performance Monitor to check the load on your CPU. Look for the Processor:% Processor Time counter: if it regularly exceeds 80% of the processor time per CPU then most probably you're facing a CPU related bottleneck.
Some of the CPU intensive operations are compilation and recompilation. You can monitor them using the SQL Statistics object counters. Also you should monitor the number of batches received. If the ratio of SQL Recompilations/sec to Batch Requests/sec is high then it potentially indicates a problem.
Setup and monitor these counters:
- SQL Server: SQL Statistics: SQL Compilations/sec
- SQL Server: SQL Statistics: SQL Recompilations/sec
- SQL Server: SQL Statistics: Batch Requests/sec
You can find more information about the SQL Statistics Object in MSDN Library.
Another counter to detect CPU related problems is the SQL Server: Cursor Manager By Type - Cursor Requests/Sec counter which shows you the cursors used on your server. If you can see hundreds of cursor requests per second then it is most probably because of poor cursor usage and small fetch sizes.
Intraquery parallelism can also be detected by examining the SQL Statistics: Batch Requests/sec counter. The less number of batches processed per second during high CPU utilization periods, the more likely the batches are running with parallel plans.
Dynamic Management Views
There are some useful Dynamic Management Views (DMVs) to check CPU bottlenecks. The sys.dm_exec_query_stats DMV shows you the currently cached batches or procedures which are using the CPU. The following query can be used to check the CPU consumption per plan_handle.
select plan_handle,
sum(total_worker_time) as total_worker_time,
sum(execution_count) as total_execution_count,
count(*) as number_of_statements
from sys.dm_exec_query_stats
group by plan_handle
order by sum(total_worker_time), sum(execution_count) desc
SQL Server 2008 computes the hash value of every query during compilation. You can find this value in the query_hash column. If two queries differ only by literal values then they should have the same query_hash value. This value is shown as the QueryHash attribute in Showplan/Statistics XML too.
The plan_generation_num column shows how many times the query has been recompiled.
The SQL Server optimizer tries to choose an execution plan for the query that provides the fastest response time but this does not always mean minimal CPU utilization. Inefficient query plans that cause increased CPU consumption can also be detected using the sys.dm_exec_query_stats.
If you would like to have an overview of how much time is spent by SQL Server with optimization then check sys.dm_exec_query_optimizer_info. The elapsed time and final cost counters are particularly useful.
You can identify intraquery parallelism and retrieve query text and execution plans by checking the subsequent DMVs:
- sys.dm_exec_cached_plans: Shows the cached query plans.
- sys.dm_exec_requests: Shows each executing request in the SQL Server instance.
- sys.dm_exec_sessions: Shows all active user connections and internal tasks.
- sys.dm_exec_sql_text: Shows the text of the SQL batches.
- sys.dm_os_tasks: Shows each active task within SQL Server.
SQL Server Profiler
You can also use SQL Server Profiler to detect unnecessary compilation and recompilation in case Performance Monitor counters point to this problem. The SQL Server Profiler Trace can help you find the recompiled stored procedures along with the reason for the recompilation. The following events contain this information:
- SP:Recompile, CursorRecompile, SQL:StmtRecompile: These events are fired when recompilations occur on your MS SQL Server. The EventSubClass data column of SP:Recompile EventClass shows the reason for the recompilation.
- Showplan XML For Query Compile: This event class occur when a Transact-SQL statement is recompiled. The query plan and the object ID of the procedure are also included. Please note that running a trace for this event can utilize significant amount of system resources. However, it is worth it to trace this event if Performance Monitor reports high SQL Compilations/sec value.
Poor cursor usage can be detected by tracing the RPC:Completed event class. Look for sp_cursorfetch statements and examine the fourth parameter. It contains the number of rows returned by the fetch.
Next Steps
- Collect and compare performance counters.
- Analyze DMV information.
- Run SQL Server Profiler.
- Check the Performance Tips category on MSSQLTips.com
About the author
Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
