1 /*****************************************************************************************
2 List heavy query based on CPU/IO. Change the order by clause appropriately
3 ******************************************************************************************/
4 SELECT TOP 20
5 DB_NAME(qt.dbid) AS DatabaseName
6 ,DATEDIFF(MI,creation_time,GETDATE()) AS [Age of the Plan(Minutes)]
7 ,last_execution_time AS [Last Execution Time]
8 ,qs.execution_count AS [Total Execution Count]
9 ,CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Total Elapsed Time(s)]
10 ,CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)]
11 ,CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)]
12 ,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU]
13 ,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
14 ,CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
15 ,CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Physical Read]
16 ,CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Reads]
17 ,CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Writes]
18 ,max_physical_reads
19 ,max_logical_reads
20 ,max_logical_writes
21 , SUBSTRING (qt.TEXT,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
22 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
23 ELSE qs.statement_end_offset
24 END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
25 , qt.TEXT AS [Batch Statement]
26 , qp.query_plan
27 FROM SYS.DM_EXEC_QUERY_STATS qs
28 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt
29 CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qp
30 WHERE qs.total_elapsed_time > 0
31 ORDER BY
32 [Total CPU time (s)]
33 --[Avg Physical Read]
34 --[Avg Logical Reads]
35 --[Avg Logical Writes]
36 --[Total Elapsed Time(s)]
37 --[Total Execution Count]
38 DESC
