Query performance troubleshooting by CPU time
SELECT TOP 20 last_execution_time, total_worker_time / execution_count as averagecputime, execution_count,
texts.*,
( total_physical_reads + total_logical_writes +
total_logical_reads ) / execution_count AS
[Total IO],
sql_handle,
plan_handle,
qp.*,
(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 , QS.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) AS texts
-- where texts.text like '%rpt_generic_perfmon%'
ORDER BY total_worker_time / execution_count DESC;
GO
texts.*,
( total_physical_reads + total_logical_writes +
total_logical_reads ) / execution_count AS
[Total IO],
sql_handle,
plan_handle,
qp.*,
(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 , QS.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) AS texts
-- where texts.text like '%rpt_generic_perfmon%'
ORDER BY total_worker_time / execution_count DESC;
GO