--使用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