SQL Performance Analysis – CPU Bottleneck (Part 1)

  • Article

SQL Server Performance Monitoring and Troubleshooting is one of the Hot Topic for our Partners/Customers. There were so many workshops we organized/delivered around this topic in last few years but still demand has not reduced. So now let’s scribble something about this on the blog in the form of FAQ:

Q: How should I start with CPU usage?

A: Start with Task Manager for %CPU Usage or Windows Performance Monitor for % Processor Time. CPU usage at around ~85% is a true indication of CPU bottleneck.

Q: What could be the reason of this high CPU usage?

A: There could be multiple reasons for the same:

·         Operating System Issue

·         SQL Server Related Issues:

      • High Recompilations of SQL Queries
      • Calculations within the Query
      • Query Design Issues: Sorting Joins, Poor Indexes, Condition Clauses etc.

Q: How do I identify that it’s not an OS issue but SQL Server issue?

A: First of all check which are the components consuming more CPU resources in your box. Ideally any other tools or server services should not be hosted in the same box with SQL Server. If SQL Server service is showing up as high CPU consuming service, we can drill down further. The following can also help where constant not zero values would represent CPU resource crunch:

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

      WHERE scheduler_id < 255

Q: Yes its SQL Server….Now what?

A: Check how much recompilations are happening on the box:

SELECT TOP 25 sql_text.text,

    sql_handle,

    plan_generation_num,

    execution_count,

    dbid,

    objectid

from sys.dm_exec_query_stats a

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

where plan_generation_num >1

      order by plan_generation_num desc

Q: What is recompilation?

A: Every time we execute a query, query goes through few specific steps:

  • Query Parsing: To make sure there are no statement level errors in the query.
  • Query Compilation/Recompilation: To generate an execution plan through which data can be pulled from the database. SQL Query Optimizer analyzes all the possible ways to get the data and the option which gives cost effective results in terms of CPU, IO and Memory usage, would be kept as execution plan for the query.
  • Query Execution: This is generally most light weight process in the entire query execution. This step would include reading through Execution Plan, pull the data and represent in user readable format.

By default Compilation and Recompilations are very system hungry. To better leverage system resources, SQL Server tends to store the previously executed queries Execution Plan in the system Memory. In case you have less system memory which would trigger SQL Memory Manager to delete unused Execution Plans or current Execution Plan not being so cost effective, query execution would trigger recompilation.

Also changes in the query text, indexes, search parameters, schema changes can also trigger recompilation.

Q: How much CPU time we have used for recompilations (optimizations) of queries.

A: Following query can help:

-- Total Optimization Performed from last restart

SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info

WHERE counter = 'optimizations';

 

-- Avergae elapsed time for Optimziation of main queries

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization

FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time'

Q: How do I get the breakup of Parsing, Compilation and Execution Time of my query?

A: Statistics Options can help. Try the below query before executing your query:

SET STATISTICS TIME ON

Q: How do I review the Execution Plan?

A: Any user query’s Execution Plan can be viewed with one of the below options:

1. Select Display Estimated Execution Plan/Include Actual Execution Plan from SSMS Tool bar.

clip_image001

2. Show Execution Plan in XML.

clip_image002

Q: How do I know that which is the Execution Plan currently stored in my system memory?

A: Following query can help:

SELECT st.text, qs. sql_handle, qs.plan_handle

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;

GO

This will show Query test, unique identified of the SQL Query and unique identified of the Execution Plan.

Q: How do I see the Execution Plan?

A: Following query can help with one of the Plan Handles which you may have captured through above step:

SELECT * FROM sys.dm_exec_query_plan (Plan Handle)

Q: How do I understand what is exactly happening in the query?

A: Enable the STATISTICS options for your Query. Specifically Profile would help.

SET STATISTICS PROFILE ON

clip_image003