Database Reference
In-Depth Information
AS
(
SELECT TOP 20
SUM(execution_count) AS QExecCount,
SUM(total_physical_reads + total_logical_reads + total_
logical_writes) AS [Total Disk IO],
SUM(total_physical_reads + total_logical_reads + total_
logical_writes) / SUM(execution_count) AS [Avg Disk IO],
MIN(query_text) AS QueryStmtText,
MIN(plan_handle) AS QPlan,
query_hash AS QueryHash,
query_plan_hash AS QueryPlanHash
FROM
(
SELECT
qs.*,
SUBSTRING(qt.[text], qs.statement_start_offset/2, (
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
) AS query_text
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle])
AS qt
WHERE qt.[text] NOT LIKE '%sys.dm_exec_query_stats%'
) AS query_stats
GROUP BY query_hash, query_plan_hash
ORDER BY [Avg Disk IO] DESC
)
SELECT
[dbid],[objectid],[QueryStmtText],[QExecCount],[Total Disk
IO],[Avg Disk IO], [query_plan]
-- TotalExecutions, [Total IO], [Avg IO],
StatementTextForExample,
tp.query_plan AS StatementPlan,
QueyHash, QueryPlanHash
FROM
CTE
OUTER APPLY sys.dm_exec_query_plan(QPlan) AS tp
WHERE [dbid]>4
ORDER BY [dbid],[Avg Disk IO],[Total Disk IO] DESC;
 
Search WWH ::




Custom Search