[SQL Server]使用DMV找出最消耗CPU/IO的SQL指令

在實務上經常會定期找出消耗CPU/IO成本比較大的Top 10 Query來進行語法調教，
以增進DB的效能和AP查詢回應的速度，以下介紹常用到的二種DMV:

--查出前十名總累積IO數最高的的Query
SELECT TOP 10
total_logical_reads+total_logical_writes AS [總IO],
execution_count AS [執行次數],
total_logical_reads AS [讀取IO數],
total_logical_writes AS [寫入IO數],
st.text AS [SQL指令]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes> 0
ORDER BY [總IO] DESC

查詢結果如下:

--查出前十名總使用CPU時間最高的的Query
SELECT  TOP 10 
qs.total_worker_time AS [總執行時間],
qs.execution_count AS [執行次數],
qs.total_worker_time / qs.execution_count AS [平均執行時間],
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 [SQL指令]
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [總執行時間] DESC;

查詢結果如下:

SQL指令調教的方法不外乎是新增缺乏的索引或定期維護索引，
再者可以針對擁有大量資料量的表格進行資料定期刪除，
又或者可將少被使用的歷史資料(例如:半年前或上一季)切到其它張表封存，
等到有需要查詢再進行存取，這作法也是可以有效的減少查詢回應的時間。