查詢性能分析較低的SQL語句
-- 查詢性能分析
SELECT TOP 50qs.creation_time AS [編譯時間],qs.last_execution_time AS [最后執行時間],qs.execution_count AS [執行次數],qs.total_worker_time/1000 AS [CPU總時間(ms)],qs.total_elapsed_time/1000 AS [總耗時(ms)],(qs.total_elapsed_time/qs.execution_count)/1000 AS [平均耗時(ms)],qs.total_logical_reads/qs.execution_count AS [平均邏輯讀],qs.total_physical_reads/qs.execution_count AS [平均物理讀],qp.query_plan AS [執行計劃],CASE WHEN qs.total_elapsed_time/qs.execution_count > 1000 THEN '嚴重'WHEN qs.total_elapsed_time/qs.execution_count > 500 THEN '警告'ELSE '正常'END AS [性能評級],SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS [執行語句]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.last_execution_time > DATEADD(HOUR, -24, GETDATE())AND st.text NOT LIKE '%sp_%'AND st.text NOT LIKE '%FETCH%'
ORDER BY [平均耗時(ms)] DESC,[執行次數] DESC;
查看 SQL 執行計劃
SET SHOWPLAN_XML ON;
GO
-- SQL語句
GO
SET SHOWPLAN_XML OFF;
GO
執行計劃關鍵解讀點：
?索引使用?
- ? Index Seek：高效索引查找
- ?? Index Scan：可能需優化索引
- ? Table Scan：全表掃描警告
?連接類型?
- Nested Loops：小數據集適用
- Hash Match：大數據連接內存消耗高
- Merge Join：需排序預處理
?警告標識?
- 紅色驚嘆號：缺失索引/統計信息過期
- 高成本百分比：性能瓶頸節點
💡 優化建議：對出現 Key Lookup 的操作創建覆蓋索引（INCLUDE 列）
