Last active
May 31, 2021 20:36
-
-
Save herdemu/d5e6720e7e5b84d8fcf83435b9dab2ef to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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