Skip to content

Instantly share code, notes, and snippets.

@herdemu
Last active May 31, 2021 20:36
Show Gist options
  • Save herdemu/d5e6720e7e5b84d8fcf83435b9dab2ef to your computer and use it in GitHub Desktop.
Save herdemu/d5e6720e7e5b84d8fcf83435b9dab2ef to your computer and use it in GitHub Desktop.
/*
Q. How can i list Cpu intensive queries?
A. You can list such queries by the help of 'sys.dm_exec_query_stats', 'sys.dm_exec_sql_text' and 'sys.dm_exec_query_plan'
Hint. Alter the @RowSize value to change the row count of the result set.
*/
DECLARE @RowSize tinyint
SET @RowSize=20
SELECT TOP (@RowSize)
DB_NAME(SqlText.dbid) AS [Database Name],
SUBSTRING
(
SqlText.text,
(ExecQStats.statement_start_offset/2)+1,
(
(
CASE
WHEN ExecQStats.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(MAX),SqlText.text)*2)
ELSE ExecQStats.statement_end_offset
END
) - ExecQStats.statement_start_offset
)/2+1
) AS [Indiviual Query],
ExecQPlan.query_plan AS [Query Plan],
ExecQStats.execution_count AS [Execution Count],
CONVERT(DECIMAL(28,2),ExecQStats.total_worker_time/1000000.0) AS [Total Cpu],
CONVERT(DECIMAL(28,2),ExecQStats.total_worker_time*100.0/ExecQStats.total_elapsed_time) AS [% Cpu wait],
CONVERT(DECIMAL(28,2),(ExecQStats.total_worker_time-ExecQStats.total_elapsed_time)*100.0/ExecQStats.total_elapsed_time) AS [% Resource wait]
FROM sys.dm_exec_query_stats AS ExecQStats
CROSS APPLY sys.dm_exec_sql_text(ExecQStats.sql_handle) AS SqlText
CROSS APPLY sys.dm_exec_query_plan(ExecQStats.plan_handle) AS ExecQPlan
WHERE ExecQStats.total_elapsed_time>0
ORDER BY [% Cpu wait] DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment