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 Query to export request details
Supported only for SQL DB(14201 build). Executed in SQL DB directly to export the requests. Below query to export requests for one account / site. SELECT "wo"."WORKORDERID" AS "Request ID", "wo"."TITLE" AS "Subject", "wotodesc"."FULLDESCRIPTION" AS ...Implementing secure gateway server for ServiceDesk Plus
As per the RBI guidelines, FOS and Secure Gateway Server features are mandatory for all hosted software used in the banking domain. We can achieve a service gateway server in the service desk plus by following the below steps: Steps to Integrate with ...