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
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 ...Enabling Read Committed Snapshot Isolation in MS SQL server
In MS SQL environments enabling 'Read committed Snapshot isolation (RCSI)' is the first step in handling application performance and crash issues. What is RCSI? Since databases handle multiple transactions in parallel, there has to be a mechanism to ...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 Backup and Restore
To take a backup of your database, do the following: Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Expand the Databases node from the left-pane aka Object Explorer. Right-click the database that you wish to ...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 ...