SELECT TOP 50
st.text AS [SQL Definition]
, SUBSTRING(
st.text,
qs.statement_start_offset / 2 + 1,
( CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2
) AS [Hot Path]
, qs.execution_count AS [Execution Count]
, qs.total_worker_time / 1000000 AS [Total CPU (s)]
, (qs.total_worker_time / $1000000) / qs.execution_count AS [Average CPU (s)]
, qs.total_elapsed_time / 1000000 AS [Total Time (s)]
, qs.total_logical_reads / qs.execution_count AS [Average Logical Reads]
, qs.total_logical_writes / qs.execution_count AS [Average Logical Writes]
, qs.total_physical_reads / qs.execution_count AS [Average Physical Reads]
, ISNULL(qp.query_plan, '') AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY [Total CPU (s)] DESC
