If your server is stressed out on CPU I am pretty sure you would benefit using these queries to correctly identify and prioritize your course of action.
The first query is the simplest as it only retrieves data from sys.sysprocesses so when a server is really stressed out on CPU and queries take long to complete this query will still work at a reasonable time. Being so simple it returns limited information but I still find it useful and it is part of my toolkit troubling sql server. For example one limitation is that it would probably miss a query that runs for a very short time is but highly executed and therefore has a cumulative high CPU usage that does impact the server. But it will definitely and capture those nasty long queries killing the server.
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('tempdb.dbo.#Processes', 'U') IS NOT NULL DROP TABLE dbo.#Processes;
CREATE TABLE dbo.#Processes (spid smallint,cpu int,ecid smallint);
INSERT dbo.#Processes (spid, cpu , ecid ) SELECT p.spid, p.cpu, p.ecid FROM sys.sysprocesses p
WAITFOR DELAY '00:00:01';
SELECT TOP 8
p.spid
,t.text
,p.cpu
,p.cpu - p2.cpu cpu_diff
,DB_NAME(p.dbid) [database]
,p.program_name
--,CASE WHEN p.[program_name] NOT LIKE 'SQLAgent%' THEN p.[program_name] ELSE (SELECT N'JobName: ' + CHAR(39) + j.name + CHAR(39) + N'; StepID:' + CAST(js.step_id as VARCHAR(12)) + N'; StepName: ' + CHAR(39) + js.step_name + CHAR(39) FROM msdb..sysjobs j INNER JOIN msdb..sysjobsteps js ON j.job_id = js.job_id WHERE j.job_id = SUBSTRING(p.[program_name],38,2) + SUBSTRING(p.[program_name],36,2) + SUBSTRING(p.[program_name],34,2) + SUBSTRING(p.[program_name],32,2) + '-' + SUBSTRING(p.[program_name],42,2) + SUBSTRING(p.[program_name],40,2) + '-' + SUBSTRING(p.[program_name],46,2) + SUBSTRING(p.[program_name],44,2) + '-' + SUBSTRING(p.[program_name],48,4) + '-' + SUBSTRING(p.[program_name],52,12) AND js.step_id = CAST( SUBSTRING(p.[program_name], 72, LEN(p.[program_name]) + 1 - CHARINDEX(')', p.[program_name]) ) as INT )) END AS [program_name]
,p.hostname
,p.loginame
,OBJECT_NAME(t.objectid, p.dbid) obj
,p.waittime
,p.lastwaittype
,p.waitresource
,p.login_time
,pl.query_plan
FROM sys.sysprocesses p
INNER JOIN dbo.#Processes p2 ON p2.spid = p.spid AND p2.ecid = p.ecid
OUTER APPLY sys.dm_exec_sql_text (p.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan (p.sql_handle) pl
ORDER BY p.cpu - p2.cpu DESC;The second query is a query written by Michael Zilberstein, a business partner who is a top class expert when it comes to sql serer query tuning and performance tuning.
This script naturally uses the sys.dm_exec_query_stats DMV but in a way I have never seen before and it’s key feature is the ability to display the number of CPU seconds each statement consumes in a 60 seconds time frame.
Note that the script calculates the AVG so in some rare cases it will not be accurate but from my experience it does the job and it is very practical.
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT TOP 50
SUM(query_stats.total_worker_time) AS [total CPU time]
,SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [avg CPU Time]
,SUM(query_stats.total_elapsed_time) / SUM(query_stats.execution_count) AS [avg Duration]
,CASE DATEDIFF(MINUTE, MIN(query_stats.creation_time), MAX(query_stats.last_execution_time))
WHEN 0 THEN 0 ELSE CONVERT(decimal(16, 2), SUM(query_stats.total_worker_time) * 1.0 / DATEDIFF(MINUTE, MIN(query_stats.creation_time), MAX(query_stats.last_execution_time)) / 1000000) END AS CPUSecondsPerMinute
,SUM(query_stats.execution_count) AS [executes]
,CASE DATEDIFF(MINUTE, MIN(query_stats.creation_time), MAX(query_stats.last_execution_time))
WHEN 0 THEN 0 ELSE CONVERT(decimal(16, 2), SUM(query_stats.execution_count) * 1.0 / DATEDIFF(MINUTE, MIN(query_stats.creation_time), MAX(query_stats.last_execution_time))) END AS executionsPerMinute
,SUM(query_stats.total_logical_reads) AS [total logical reads]
,SUM(query_stats.total_logical_reads) / SUM(query_stats.execution_count) AS [avg logical reads]
,SUM(query_stats.total_physical_reads) AS [total physical reads]
,SUM(query_stats.total_physical_reads) / SUM(query_stats.execution_count) AS [avg physical reads]
,SUM(query_stats.total_logical_writes) AS [total logical writes]
,SUM(query_stats.total_logical_writes) / SUM(query_stats.execution_count) AS [avg logical writes]
,SUM(query_stats.total_rows) / SUM(query_stats.execution_count) AS [avg rows]
,SUM(query_stats.total_grant_kb) / SUM(query_stats.execution_count) AS [avg grant kb]
,MIN(query_stats.creation_time) AS creation_time
,MAX(query_stats.last_execution_time) AS last_execution_time
,MAX(query_stats.ProcName) AS ProcName
,MAX(query_stats.DBName) AS DBName
,MIN(query_stats.statement_text) AS [statement text]
,query_stats.query_hash
FROM
(
SELECT QS.*
,OBJECT_NAME(ST.objectid, ST.[dbid]) AS ProcName
,DB_NAME(ST.[dbid]) AS DBName
,SUBSTRING( ST.text
,(QS.statement_start_offset / 2) + 1
,((CASE statement_end_offset
WHEN-1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset
) / 2
) + 1
) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
) AS query_stats
GROUP BY query_stats.query_hash
HAVING MAX(query_stats.last_execution_time) >= DATEADD(MINUTE, -10, GETDATE())
--AND SUM(query_stats.execution_count) >= 100
--AND SUM(query_stats.execution_count) >= DATEDIFF(MINUTE, MIN(query_stats.creation_time), MAX(query_stats.last_execution_time))
ORDER BY CPUSecondsPerMinute DESC;Once you have identified the statement you want to look into and improve you can use this query to get it’s estimated query plan from sys.dm_exec_text_query_plan
Note that you have to copy past your query hash at the bottom of the query.
SELECT TOP 50
query_stats.total_worker_time AS [total CPU time]
,query_stats.total_worker_time / (query_stats.execution_count * 1000) AS [avg CPU Time MS]
,query_stats.total_elapsed_time / (query_stats.execution_count * 1000) AS [avg Duration MS]
,CASE DATEDIFF(MINUTE, query_stats.creation_time, query_stats.last_execution_time)
WHEN 0 THEN 0 ELSE CONVERT(decimal(16, 2), query_stats.total_worker_time * 1.0 / DATEDIFF(MINUTE, query_stats.creation_time, query_stats.last_execution_time) / 1000000) END AS CPUSecondsPerMinute
,query_stats.execution_count AS [executes]
,CASE DATEDIFF(MINUTE, query_stats.creation_time, query_stats.last_execution_time)
WHEN 0 THEN 0 ELSE CONVERT(decimal(16, 2), query_stats.execution_count * 1.0 / DATEDIFF(MINUTE, query_stats.creation_time, query_stats.last_execution_time)) END AS executionsPerMinute
,query_stats.total_logical_reads AS [total logical reads]
,query_stats.total_logical_reads / query_stats.execution_count AS [avg logical reads]
,query_stats.total_logical_writes AS [total logical writes]
,query_stats.total_logical_writes / query_stats.execution_count AS [avg logical writes]
,query_stats.creation_time AS creation_time
,query_stats.last_execution_time AS last_execution_time
,query_stats.ProcName AS ProcName
,query_stats.DBName AS DBName
,query_stats.statement_text AS [statement text]
,TRY_CONVERT(XML, query_stats.query_plan) AS ExecPlan
,query_stats.query_hash
FROM
(
SELECT QS.*
,OBJECT_NAME(ST.objectid, ST.[dbid]) AS ProcName
,DB_NAME(ST.[dbid]) AS DBName
,CONVERT(nvarchar(MAX), p.query_plan) AS query_plan
,SUBSTRING( ST.text
,(QS.statement_start_offset / 2) + 1
,((CASE statement_end_offset
WHEN-1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset
) / 2
) + 1
) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
OUTER APPLY sys.dm_exec_text_query_plan(QS.plan_handle, QS.statement_start_offset, QS.statement_end_offset) AS p
) AS query_stats
WHERE query_stats.last_execution_time >= DATEADD(MINUTE, -10, GETDATE())
AND query_stats.query_hash = 0x9689CE0A664A246C --<------------ Paste your query hash here
--AND query_stats.ProcName = ''
ORDER BY 1 DESC;The following two tabs change content below.
Yaniv Etrogi is an SQL Server consultant. He loves SQL Server and he is passionate about Performance Tuning, Automation and Monitoring.
Latest posts by Yaniv Etrogi (see all)
- Monitor AlwaysOn Availabilty Groups - July 6, 2023
- SQL Server – The secret index syntax - February 8, 2023
- Use .net SqlClient with Powershell to access data - January 25, 2023
- Use Powershell to find unused resources in Azure - August 5, 2022