/*****************************************************************************************
List heavy query based on CPU/IO. Change the order by clause appropriately
******************************************************************************************/SELECT TOP 20DB_NAME(qt.dbid) AS DatabaseName,DATEDIFF(MI,creation_time,GETDATE()) AS [Age of the Plan(Minutes)],last_execution_time AS [Last Execution Time],qs.execution_count AS [Total Execution Count],CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Total Elapsed Time(s)],CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)],CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)],CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)],CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Physical Read],CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Reads],CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Writes],max_physical_reads,max_logical_reads,max_logical_writes, SUBSTRING (qt.TEXT,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query], qt.TEXT AS [Batch Statement], qp.query_planFROM SYS.DM_EXEC_QUERY_STATS qsCROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qtCROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qpWHERE qs.total_elapsed_time > 0ORDER BY [Total CPU time (s)] --[Avg Physical Read]--[Avg Logical Reads]--[Avg Logical Writes]--[Total Elapsed Time(s)]--[Total Execution Count]DESC
Let us discuss the output columns.
| Column Name | Description |
| DatabaseName | Database Context of the execution plan. |
| Age of the Plan(Minutes) | Age of the plan in the plan cache in minutes. |
| Last Execution Time | Date and Time of the last execution of this plan. |
| Total Execution Count | Total number of execution after the last compilation Or Total number of execution in the last [Age of the Plan(Minutes)] (since it was last compiled) |
Total Elapsed Time(s) | Total time (second) took to execute this plan [Total Execution Count] times |
Average Execution time(s) | Average Time(seconds) took for single execution of this plan |
| Total CPU time (s) | Total CPU time(seconds) took to execute this plan [Total Execution Count] times |
| % CPU | Percentage of CPU time compared to [Total Elapsed Time(s)] |
% Waiting | Percentage of waiting time(wait for resource) compared to [Total Elapsed Time(s)] |
CPU time average (s) | Average CPU time (Seconds) used for single exection |
Avg Physical Read | Average number of Physical read for single execution |
Avg Logical Reads | Average number of Logical read for single execution |
Avg Logical Writes | Average number of Logical writes for single execution |
max_physical_reads | Maximum number of physical reads that this plan has ever performed during a single execution. |
max_logical_reads | Maximum number of logical reads that this plan has ever performed during a single execution. |
max_logical_writes | Maximum number of logical writes that this plan has ever performed during a single execution. |
Individual Query | Part of Batch Statement |
Batch Statement | Batch Query |
query_plan | XML execution . On clicking this we can see the graphical execution plan |
If you liked this post, do like my page on FaceBook
