Identify Top 50 queries running with execution count
Use Master
go
SELECT TOP 50 creation_time, last_execution_time, total_clr_time,total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CLR Time] DESC;
GO
go
SELECT TOP 50 creation_time, last_execution_time, total_clr_time,total_clr_time/execution_count AS [Avg CLR Time], last_clr_time, execution_count, (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CLR Time] DESC;
GO
Dear Vijay Sir,
ReplyDeleteI really appreciate you.Thanks for this content.Its very useful to me.