--使用dmv找出最消耗IO的查詢
/*
logical_reads:查詢時從DATA
CACHE讀取PAGE
physical_reads:查詢時從DATA
CACHE讀取PAG從硬碟讀取資料
*/
SELECT TOP 10
[IO總和] = (A.total_logical_reads +
A.total_physical_reads+ A.total_physical_reads)
,[平均IO] =
(A.total_logical_reads + A.total_physical_reads)/A.execution_count
,A.execution_count
[執行次數]
,B.[text]
[SQL指令]
FROM
sys.dm_exec_query_stats A
CROSS APPLY
sys.dm_exec_sql_text(A.sql_handle) as B
WHERE B.[text] NOT
LIKE '%SCHEMA_NAME(%'--去除系統的SQL指令
ORDER BY [IO總和]
DESC
文章定位: