An way to isolating SQL high CPU concerns is summarised in the screenshot below:
Microsoft recommends the following CHECKLIST POINTS:
1. Check the overall CPU usage via the Windows task manager. Gather information on the number of logical processors on the box.
2. Check the SQL Server process CPU consumption in Task Manager. Is the SQL CPU always at or above 70%?
3. Collect the following information:
- How do you usually realise your CPU is a bottleneck?
- What is the problem’s impact? Are there any specific errors that your user application encounters?
- When did the issue initially appear? Is there anything that changed around this period that you’re aware of? (Has your workload increased? Changes to the table’s dimensions? Is it time to update your app? (Is there a SQL upgrade?)
- Is it possible to establish new connections to the server during the outage?
- How long did the issue persist? Have you been able to come up with anything that appears to be helpful in resolving the issue?
- During the issue periods, what system-level symptoms did you notice? Is the server console, for example, slow or unresponsive during the problematic periods? Is there an increase in overall CPU usage? If that’s the case, what percentage of CPU is used during the problem? What percentage of the CPU is expected to be used?
4. f a process other than SQL Server (sqlservr.exe) is producing the high CPU, contact the team in charge of that process.
5. Open Perfmon and add the counters listed below:
% Privileged Time
% Processor Time
% User Time
% Privileged Time
% Processor Time
% User Time
6. Engage the Windows team if Processor Privileged time exceeds 25%.
Processor Time = Privileged Time + User Time.
7. Verify that SQL is using a lot of CPU on the box by looking at the counters below:
Process (sqlservr): % Privileged Time
% Processor Time
% User Time
The CPU consumption by SQL Process is calculated by dividing the observed value by the number of logical processors.
If (Process (sqlservr) % Privileged time/No of Procs) is greater than 30%, make sure that KB 976700 for Windows 2008 R2 is installed.
This phase determines whether SQL Server is the source of the server’s high privilege time. Engage the Windows team if SQL privilege time is high, as determined by the calculations above.
8. Check the following sp_configure configurations for compliance with best practice recommendations:
For Max DOP recommended settings, see KB 2806535.
9. If you can’t connect to the SQL instance locally using SSMS, try connecting via Dedicated Admin Connection (DAC) with the following commands:
10. Using the following query, get the top 10 queries that use a lot of CPU:
SELECT s.session_id, r.status, r.blocking_session_id 'Blk by', r.wait_type, wait_resource, r.wait_time / (1000 * 60) 'Wait M', r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', Substring(st.TEXT,(r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time desc
11. Examine the wait type of the queries in the report above. If the CPU is the bottleneck, most sessions will experience the following delays:
If most queries are waiting on CXPACKET, examine the “Max degree of parallelism” and “Cost degree of parallelism” settings in sp configure to see if they are set according to best practice recommendations.
12. To retrieve a list of the top CPU queries, run the SQL Standard report:
Right Click on the instance, go to reports> Standard reports
In the report, look at the top CPU demands. Compare the report to the list of the top CPU-intensive requests acquired in the previous phase.
13. Once the top CPU queries have been discovered, use the statement_text and command_text column output from step 10 to generate a list of all SQL tables involved.
Examine the following:
Examine the top CPU-driven tables for Index Fragmentation.
when was the last time the statistics were updated?
Rebuild the index if the fragmentation is greater than 30%. Update the statistics on the table if they haven’t been updated.
If there are only a few tables responsible for high CPU in the Top CPU queries, share the tables list with the application team, as well as the statistics and fragmentation reports.
Check if there are any select queries that are consuming a lot of CPU, and ask the application team if these may be temporarily stopped on high-performance OLTP servers.
14. If SQL is still using a lot of CPU after the database maintenance activity (such Index rebuild and Stats update), run the query specified in Step 10.
Examine the Top CPU query to see whether it has changed. If the query has changed, go to Step 13 and take the appropriate action. Continue to the following step if the query remains the same.
15. Using the following methods, gather the expected execution plans for the top CPU-intensive queries:
Query 1: From the output of the query mentioned in step 10, get the top CPU using session IDs.
Take note of the Plan handle and SQL handle from the following query:
select sql_handle,plan_handle from sys.dm_exec_requests where session_id=<session_id>
Obtain the query’s text:
–Replace the SQL Handle with the result of the query above.
select * from sys.dm_exec_sql_text (sql_handle)
Get the query’s estimated execution plan:
–Replace the Plan handle with the result of the query above.
select * from sys.dm_exec_query_plan (plan_handle)
Query 2: The query below records the inquiry’s total CPU time as well as the plan handle. The query’s plan handle is required to obtain the query’s expected execution time.
select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc
16. With the application team, provide the estimated execution plan.
Look for a Operator that has a high cost.
Check the operator’s indexes and the predicted amount of rows.
Check the statistics and indexes on the table for the operator with the highest cost to ensure there are no stale statistics.
Check to see if the expected execution plan suggests creating a new index. Share the missing index details with the Application team if the plan provides an index recommendation.
17. The “Convert Implicit” function in the execution plan can also cause SQL Server to use a lot of CPU.
Examine the execution plans for queries that use a lot of CPU, as well as the Operator with a High Cost, to see if the Convert Implicit function is used..
The CONVERT IMPLICIT function implicitly converts the column “NationalIDNumber” to integer in the preceding snapshot, despite the fact that it is defined as nvarchar in the table definition (15). So, share the report with the application team and double-check that the data type provided and the data type kept in the database are the same.
18. Run the missing index query on the database that has a high CPU use report to see whether any missing indexes are recommended. Inform the Application team about the Index suggestion report.
19. Use the Database Engine Tuning Adviser to tune the top CPU-intensive queries to check if the database engine advises index creation or statistics creation.
20. Compilations/Re-Compilations in SQL Server :
Take the following counters from perfmon:
SQL Server: SQL Statistics: Batch Requests/sec
SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
Batch Requests/sec: Number of SQL batch requests received by server.
SQL Compilations/sec: Number of SQL compilations per second.
SQL Recompilations/sec: Number of SQL re-compiles per second.
If the number of recompilations is high, look for the following:
Any Schema changes
SET option changes in the batch
Temporary table changes
The RECOMPILE query hint or the OPTION (RECOMPILE) query hint are used to create stored procedures.
Add the following events to SQL profiler and look for stored procedures that are regularly recompiled.
21. Check to see if SQL System threads are using a lot of CPU:
select * from sys.sysprocesses where cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'
Ghost cleanup thread >>>> Examine whether the user deleted a huge number of rows.
Lazy Writer thread >>>>>>> Check if the server is experiencing any memory constraints.
Resource Monitor thread >> Examine whether the server is experiencing any memory constraints.
22. Check if any traces are active on the server if the Top CPU consuming queries have the wait type: SQLTRACE LOCK.
select * from sys.traces
23. During the Top CPU issue, collect the PSSDIAG. See KB 830232 for more information. In the SQL Nexus tool, load and analyse the data..
24. If the SQL CPU utilisation is still high after completing the preceding action plans, increase the server’s CPU.