How to identify CPU bottlenecks in SQL Server

Original: How to identify CPU bottlenecks in SQL Server

Original from:

http://www.mssqltips.com/sqlservertip/2316/how-to-identify-sql-server-cpu-bottlenecks/

Problem:

If SQL Server outages are frequently encountered as a result of CPU bottlenecks, how do you identify and resolve these related issues?

Solution:

There are a number of reasons why the CPU has become a SQL Server performance issue, and the obvious reason is because of insufficient resources. However, CPU utilization can be reduced by configuration changes and query optimizations, so consider the previous action before you want to buy a faster and better processor. Here are some built-in tools to identify CPU-related bottlenecks:

Performance Monitor (Performance Monitor):

You can use Performance Monitor to check the load on the CPU. Check the processor:% Processor Time counter: If you exceed the 80%/processor over the long term, you are likely to face CPU-related bottlenecks.

CPU intensive operations are primarily compiled and recompiled. You can monitor their situation by using SQL Statistics object counters. You can also monitor the number of batches received for viewing. If the rate of batchrequests/sec in SQL recompilations/sec is high, there is a potential problem:

Configure and monitor the following counters:

• SQL server:sql statistics:sql compilations/sec
• SQL server:sql statistics:sql recompilations/sec
• SQL Server:sql Statistics:batch requests/sec

You can get more information about this section from MSDN: the MSDN Library.

Another counter for detecting CPU-related problems is theSQL server:cursor Manager by Type–cursorrequests/sec , which displays your server's upstream label usage. If you see hundreds of cursor requests per second, it is likely that performance issues are caused by inefficient cursor usage and small-volume fetch operations (small fetch size).

Internal parallel queries also cause CPU problems and can be checked for:

SQL statistics:batch requests/sec counter counters. The batch processing per second should be small in the CPU life cycle. If too much, it means that you are running with a parallel plan.

dynamic management view (DMVs):

The following is a DMVs for troubleshooting CPU bottleneck swimming. Dynamic View: Sys.dm_exec_query_stats Displays the currently cached batches or processes that use the CPU. The following query is used to check the CPU-intensive execution plan:

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 Bysum (Total_worker_time), sum (execution_count) desc

SQLSERVER2008 calculates the hash value of each query when it is compiled. You can find this value in the Query_hash column, whether the two queries have only a different literal value but use the same query_hash values. This value can also be viewed in the showplan/statistics XML Queryhash property.

The Plan_generation_num column shows the number of times a query was recompiled.

The SQL Server optimizer attempts to select an execution plan that provides the fastest response time, but does not always mean low CPU utilization. Inefficient query plans can cause the CPU to work well, and this could be monitored using sys.dm_exec_query_stats .

If you want to have an overview of the time that is spent on SQL Server optimization, you can check:

sys.dm_exec_query_optimizer_info . The time spent and the last overhead can be very useful.

You can use the following DMVs to query the internal parallel query and its query text, execution plan:

• Sys.dm_exec_cached_plan :  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:

If Performance Monitor finds a problem, you can also use SQL Server Profiler to discover unnecessary compilation and recompilation. SQL Server Profiler Tracking can help you find stored procedures that have been recompiled. You can use the following events:

• Sp:recompile , cursorrecompile, sql:stmtrecompile: This event is a recompile for SQL Server. The EventSubClass in the sp:recompile event illustrates the reason for recompilation.

· Showplan XML for Query Compile: This event is a recompilation of T-SQL statements. Contains the object ID of the query plan and procedure. Note that running a trace on this event can get important information about using the system resources. However, if the performance counters report a high value for SQL compilations/sec , tracing will be a very good resource.

Inefficient cursors can be traced using the Rpc:completed event. Review the Sp_cursorfetch statement and examine the fourth parameter, which contains the number of rows per advance (fetch).

How to identify CPU bottlenecks in SQL Server