How to Identify SQL Server CPU Bottlenecks

By:   |   Updated: 2011-03-01   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tibor Nagy 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


Article Last Updated: 2011-03-01

Comments For This Article




Tuesday, April 3, 2018 - 9:51:16 PM - Miguel Back To Top (75595)

Please change sys.dm_exec_cached_plan to sys.dm_exec_cached_plans

Thursday, January 23, 2014 - 8:58:42 AM - Tibor Nagy Back To Top (28191)

Jai,

There is already a tip about how to reduce the excess compilation and recompilation in SQL Server: http://www.mssqltips.com/sqlservertip/2998/troubleshooting-excess-compilations-in-sql-server-using-the-plan-cache-and-powershell/

Thursday, January 23, 2014 - 8:08:35 AM - Jai Back To Top (28189)

Hi,

I have a machine on which the SQL Recompilations/sec are 20% of the Batch Requests/sec,Can you please suggest what should I do now in order to fix it .?

I am thinking of updating stats at the moment....Am I on the right path ?

 

 

Wednesday, March 2, 2011 - 3:39:28 AM - Tibor Nagy Back To Top (13084)

Hi SQL_Learner,

 

I would say that SQL Recompilations/sec should be less than 1% of Batch Requests/sec value. So in case your system has 1000 Batch Requests/sec then I consider 20 SQL Recompilations/sec high.

 

Regards,

Tibor

Tuesday, March 1, 2011 - 4:53:28 PM - SQL_Learner Back To Top (13079)

Hi Tibor,

 

Really good article.

One question if you say "If the ratio of SQL Recompilations/sec to Batch Requests/sec is high then it potentially indicates a problem." What number we are looking at to say it is high ?

Thanks and regards,

SQL_Learner














get free sql tips
agree to terms