SQL Server High CPU - To collect queries consuming CPU resources
1. To get an idea of how much CPU the queries are currently using, out of overall CPU capacity, run the following statement:
DECLARE @init_sum_cpu_time int,
@utilizedCpuCount int
--get CPU count used by SQL Server
SELECT @utilizedCpuCount = COUNT( * )
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
--calculate the CPU usage by queries OVER a 5 sec interval
SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests
WAITFOR DELAY '00:00:05'
SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity]
FROM sys.dm_exec_requests
2. To identify the queries that are responsible for high-CPU activity currently, run the following statement:
SELECT TOP 10 s.session_id,
r.status,
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
3. If queries aren't driving the CPU at this moment, you can run the following statement to look for historical CPU-bound queries:
SELECT TOP 10 qs.last_execution_time, st.text AS batch_text,
SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
(qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
(qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
(qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
(qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC
Article Link: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues
Article Link: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues
New to ADSelfService Plus?
Related Articles
How to move the application to another server when you have a MSSQL Database ?
1. Since you have MSSQL database, take a .BAK backup from the existing SQL server and restore the data on to the new SQL server in the SQL Studio. Follow the regular steps of restoring Microsoft database. 2. On your new server, Install the 64 bit ...High CPU Utilization during Backup
Cause: From 14740, below listed parameters will no longer be included in the command. As a result, when performing backups, 7-Zip utilizes a high number of CPU threads, leading to increased CPU usage. mmt - Sets number of CPU threads mmemuse - Sets a ...Migration of SCP from one server to another
1. Stop the application on the production server. 2. Take a trimmed backup of the application (backup without attachments) if you are using the default PgSQL database. If you are using MSSQL, you can take a SQL backup (.bak). 3. Download and install ...SQL Master Key Password FAQs
How to create the master key password. The user with dbcreator permissions can follow these steps to create the master key password: Open MSSQL Server Management Studio, right-click the database and choose New Query In the workspace, run the ...Performance debugging from version 9306 onwards
On Performance/Crash Issues, we have one URL to generate all debug details, this is applicable from 9306 onwards. Need to execute the URL in the browser, which has already login session into the application ...