Troubleshooting High CPU issues in SQL Server (Part 2)

If you ever ask a SQL Server DBA about most frequently faced performance issue in SQL Server, they would most likely say blocking or high CPU. A smart DBA would first confirm that high CPU is caused by SQL Server by looking at performance monitor. Once it is confirmed who is consuming high amount of CPU, next logical action would be to find out the query which is causing this. There are multiple DMVs in SQL Server which can tell current activity in SQL Server. A naïve DBA would use activity monitor or some other tool to monitor currently executing queries and find the top CPU consumer.

An expert DBA would go one step further and start using DMVs to get to the root of the problem. Someone righty said – Performance tuning of SQL Server is an art. Once should know little bit internal working of SQL Server to master this art.

Here are the common patterns which I have seen based on my experience which can cause high CPU in SQL Server. This is in continuation to the previous blog on CPU issues.

Inefficient query execution plans

1. Missing/out of date statistics – Wrong cardinality estimates

This is generally caused when the data has changed considerably in the tables used by queries and the same change is not reflected in the statistics. Imagine you have to go from your home to airport and there are many routes to reach airport. Given a choice, you would always love to take the route which would make your travel time as low as possible (unless you love long drives). To make that choice, you need to find current road condition, traffic condition and few more factors. If you don’t have latest data, you might end up in choosing a route which is very busy today. If gathered statistics are not up-to-date, you might make a wrong plan choice. SQL Server query optimizer does the same thing. It would not look at actual data in table to make a choice of seek vs scan, nested loop vs hash join, serial plan vs parallel plan etc. It would rather look at statistics available with the index/column and make a choice. So, we need to make sure statistics are up-to-date all the time.

To update statistics for a tables in the database, you can use below query.

Update Statistics <tableName> With FullScan

2. Improper Indexes/statistics on the tables.

Once the statistics are up-to-date and we see query consuming considerable amount of CPU then logically we should move to next step to find if there is any index which can help optimizer in getting the data faster. The most easy-to-use tool in such situation is Database Engine Tuning Advisor (DTA). It can grab the query and run bunch of rules to ask SQL Server “how would you perform if I had this index?” Since SQL Server optimizer is closely coupled in using DTA, the recommendation are mostly accurate.

Excessive compilation and recompilations

Whenever a query is submitted to SQL Server for execution, it has to generate or reuse the query plan. Generation of query plan is called as compilation and this is a CPU intensive operation. If we have an OLTP system which has thousands of queries running per second and if each query has to compile then then compilation can be a problem. After analyzing the trace captured during problem causing time, if the cause of high CPU is excessive compilation of similarly looking ad hoc batches then a DBA may enable parameterization at the database or query level by using the PARAMETERIZATION FORCED database option or query hint. This is one of the reason where application architect suggest to use stored procedures to do repetitive task or use sp_executesql.

Handling recompilation is little tricky as we need to capture the cause of recompilation. There could be various reasons which can be found using profiler trace. In profiler, we can capture Sp:Recompile, CursorRecompile and SQL:StmtRecompile events and look at Event SubClass column to find the actual cause. Based on the cause appropriate action can be taken.

High IO queries

This would sound logical because doing IO also consumes CPU cycles. Once we identify the query, we can look scans which are for large amount of rows and check if that can be reduced by creating some indexes. Again, database engine query advisor can come to rescue and provide some recommendations.

Here are two useful queries which should help in such issues.

1. Identifying high CPU queries from cache. We can execute the following query to find the TOP 50 cached plans that have consumed the most cumulative CPU. All times are in microseconds.

 

SELECT TOP 50 qs.creation_time, 
              qs.execution_count, 
              qs.total_worker_time AS total_cpu_time, 
              qs.max_worker_time   AS max_cpu_time, 
              qs.total_elapsed_time, 
              qs.max_elapsed_time, 
              qs.total_logical_reads, 
              qs.max_logical_reads, 
              qs.total_physical_reads, 
              qs.max_physical_reads, 
              t.[text], 
              qp.query_plan, 
              t.dbid, 
              t.objectid, 
              t.encrypted, 
              qs.plan_handle, 
              qs.plan_generation_num 
FROM   sys.dm_exec_query_stats qs 
       CROSS apply sys.Dm_exec_sql_text(plan_handle) AS t 
       CROSS apply sys.Dm_exec_query_plan(plan_handle) AS qp 
ORDER  BY qs.total_worker_time DESC   

 

2. Identifying high CPU queries which are currently executing. This would get data from requests DMV which shows currently execution queries.

SELECT sessions.session_id
    ,requests.status
    ,requests.blocking_session_id 
    ,requests.wait_type
    ,wait_resource
    ,requests.wait_time / (1000.0) 'wait_time(sec)'
    ,requests.cpu_time
    ,requests.logical_reads
    ,requests.reads
    ,requests.writes
    ,requests.total_elapsed_time / (1000.0) 'total_elapsed_time(Sec)'
    ,Substring(sqltext.TEXT, (requests.statement_start_offset / 2) + 1, (
            (
                CASE requests.statement_end_offset
                    WHEN - 1
                        THEN Datalength(sqltext.TEXT)
                    ELSE requests.statement_end_offset
                    END - requests.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(sqltext.dbid)) + N'.' + Quotename(Object_schema_name(sqltext.objectid, sqltext.dbid)) + N'.' + 
     Quotename(Object_name(sqltext.objectid, sqltext.dbid)), '') AS command_text
    ,requests.command
    ,sessions.login_name
    ,sessions.host_name
    ,sessions.program_name
    ,sessions.host_process_id
    ,sessions.last_request_end_time
    ,sessions.login_time
    ,requests.open_transaction_count
FROM sys.dm_exec_sessions AS sessions
INNER JOIN sys.dm_exec_requests AS requests ON requests.session_id = sessions.session_id
CROSS APPLY sys.dm_exec_sql_text(requests.sql_handle) AS sqltext
WHERE requests.session_id != @@SPID
ORDER BY requests.cpu_time DESC

 

This blog should have given you a quick start and explained to take logical approach to solve high CPU issues.