Microsoft recommends the following CHECKLIST POINTS:
- Use the Windows task manager to check the overall CPU use. Count the box’s logical processors to get a precise count.
- Examine Task Manager’s SQL Server process CPU use. Is the SQL CPU typically at or higher than 70%?
- Compile the following data:
- How do you typically detect a bottleneck in your CPU?
- What are the effects of the issue? Are there any particular mistakes that your user application makes?
- When did the problem start to show up? Do you know of any changes that occurred during this time? Has your workload grown lately? Dimensional changes to the table? Should your app be updated right now? (Has SQL been upgraded?)
- Is it still feasible to connect to the server while it’s down?
- How long did the problem last? Have you thought of anything that seems to be helpful in solving the problem?
- What symptoms at the system level did you see throughout the problem periods? For instance, is the server console sluggish or unresponsive during the issue times? Has the overall CPU utilization increased? What portion of the CPU is being used when the issue occurs, if that’s the case? What portion of the CPU should be utilized?
- If a different process than SQL Server (sqlservr.exe) is using a lot of CPU, get in touch with the team in charge of that process.
- Open Perfmon and add the following counters:
Process (sqlservr):
% Privileged Time
% Processor Time
% User Time
- If Processor Privileged time exceeds 25%, contact the Windows team.
Privileged time plus user time equals processor time. - Check the counters below to confirm that SQL is consuming a lot of CPU on the box:
% Privileged Time % Processor Time % User Time Process (sqlservr)
The observed value is divided by the number of logical processors to determine the CPU usage by the SQL Process.
Install KB 976700 for Windows 2008 R2 if (Process (sqlservr)% Privileged time/No of Procs) is larger than 30%.
This step detects if the server’s high privilege time is coming from SQL Server. If the calculations show that the SQL privilege time is high, involve the Windows team.
- Verify the following sp configure configurations to see if they adhere to the best practise guidelines:
See KB 2806535 for the Max DOP suggested settings. - Try connecting through Dedicated Admin Connection (DAC) with the following commands if you are unable to connect to the SQL instance locally using SSMS:
SERVER NAME, ADMIN - Get the top 10 queries that utilise a lot of CPU by using the following query:
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
- Look at the queries’ wait types in the report above. The majority of sessions will suffer the following delays if the CPU is the bottleneck:
SOS SCHEDULER YIELD
CXPACKET
THREADPOOL
Check the “Max degree of parallelism” and “Cost degree of parallelism” options in sp configure to determine if they are set in accordance with best practise guidelines if the majority of your queries are waiting on CXPACKET. - Run the SQL Standard report to get a list of the top CPU queries:
Right Select the instance and then select Reports > Standard Reports.
Look at the top CPU needs in the report. Examine the report against the ranking of the most CPU-intensive requests that was gathered at the earlier stage.
- Use the statement text and command text column data from step 10 to create a list of all the SQL tables involved after identifying the top CPU queries.
Look at what follows:
Check for Index Fragmentation in the top-performing CPU-driven tables.
When were the numbers most recently revised?
If the fragmentation is higher than 30%, rebuild the index. If the statistics in the table have not been updated, do so now.
Share the tables list, together with the statistics and fragmentation reports, with the application team if only a small number of tables are to blame for the Top CPU queries’ excessive CPU usage.
Look for any specific queries that are using a lot of CPU and inquire with the application team about if they may be temporarily delayed. - Run the query described in Step 10 if SQL is still consuming a significant amount of CPU after the database maintenance activity (such as Index rebuild and Stats update).
- Check to see whether the Top CPU query has changed. Go to Step 13 and take the required action if the question has changed. If the query does not change, move on to the next step.
- Compile the anticipated execution plans for the top CPU-intensive queries using the following techniques:
- Get the top CPU utilising session IDs from the results of the query mentioned in step 10 in the first query.
- The Plan handle and SQL handle from the following query should be noted:
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 following query logs the plan handle and the overall CPU time used by the enquiry. To get the projected execution time of the query, the plan handle must be provided.
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
- Share the estimated execution plan with the application team.
Find an operator with a high cost.
Verify the indexes of the operator and the expected number of rows.
For the operator with the greatest cost, check the statistics and indexes on the database to make sure there are no outdated statistics.
Check to check if a new index should be created according to the anticipated execution strategy. If the plan recommends an index, inform the application team of the missing index information. - Another factor that can make SQL Server consume a lot of CPU is the “Convert Implicit” function in the execution plan.
Check to check if the operator with a high cost and queries that consume a lot of CPU have similar execution plans.
Despite being defined as nvarchar in the table definition, the column “NationalIDNumber” in the above snapshot is implicitly converted to an integer by the CONVERT IMPLICIT function (15). Therefore, make sure the data type provided and the data type retained in the database are the same by sharing the report with the application team.
- Check the database using a high CPU utilisation report using the missing index query to check if any missing indexes are suggested. Share the Index recommendation report with the Application team.
- Check to see if the database engine recommends creating an index or statistics using the Database Engine Tuning Adviser on the top CPU-intensive queries.
- SQL Server compilations and recompilations:
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
Statistics 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.
Examine whether SQL System threads are consuming a lot of CPU power.
select * from sys.sysprocesses where cmd like ‘LAZY WRITER’ or cmd like ‘%Ghost%’ or cmd like ‘RESOURCE MONITOR’
Thread for ghost removal >Check to see if the user erased a significant amount of rows.
Thread for Lazy Writers > See if there are any memory restrictions on the server.
Thread for Resource Monitor >> Check to see if there are any memory restrictions on the server.
- Verify whether any traces are running on the server if the most CPU-intensive searches are wait-type queries: DBTRACE LOCK
select * from sys.traces- Collect the PSSDIAG when the Top CPU problem is present. Check out KB 830232 for more details. Load the data into the SQL Nexus tool and then analyse it.
- Increase the server’s CPU if the SQL CPU utilisation is still high after completing the above action plans.