SQL Server 三大典型等待類型診斷實戰：Memory、Latch、Lock 根因分析與索引優化

在雙 11 大促前夕，作者整理了 SQL Server 實戰中最常見的三大等待類型——MEMORY、LATCH、LOCK——的診斷思路與解決案例，助你快速定位效能瓶頸。

案例背景

RDS 實例 24 GB 記憶體，突發大量 waiting tasks，CPU 從 10% 飆至 90%。

一、MEMORY 等待：缺索引導致掃描過大，記憶體授予排隊

現象

Resource Waits 顯示大量 MEMORY 等待，調大記憶體至 36 GB 無效，CPU 飆高。

定位 SQL

-- 找出正在等待記憶體授予的查詢
SELECT st.text 
FROM sys.dm_exec_query_memory_grants AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS st
WHERE req.grant_time IS NULL OR req.granted_memory_kb IS NULL;

鎖定高邏輯讀、高頻 SQL：

SELECT * FROM RefundOrder_Message WHERE Order_Id = @p0;

根因與解決

• RefundOrder_Message 僅有 PK，Order_Id 無索引 → 全表掃描 → 需大量記憶體排序/雜湊 → 記憶體授予排隊。
• 建立索引：CREATE INDEX ind_RefundOrder_Message_order_id ON RefundOrder_Message(Order_Id);
• 效果：Memory 等待即刻消失，Batch Requests/sec 顯著提升。

二、LATCH 等待：熱頁競爭，同樣源於缺索引

現象

加上索引後 Memory 等待消失，但 Resource Waits 轉為 LATCH_EX / PAGEIOLATCH。

定位阻塞鏈

SELECT ss.host_name, req.blocking_session_id, req.wait_type, 
       req.wait_time, req.wait_resource, st.text
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) st
CROSS APPLY sys.dm_exec_sessions ss
WHERE req.status = N'suspended' 
  AND ss.session_id = req.session_id;

阻塞 SQL：

SELECT TOP (@p0) * FROM ViewSalesOrder 
WHERE MemberCode = @p1 AND IsObsolete = @p2 
ORDER BY OdCode DESC;


根因與解決

    
ViewSalesOrder 關聯多張核心表，MemberCode、IsObsolete、OdCode 來自 SalesOrder 表且無索引。
    
建立覆蓋索引：CREATE INDEX ind_salesOrder_member ON SalesOrder(MemberCode, IsObsolete, OdCode);
    
效果：Waiting Tasks 驟降，吞吐量回升。


三、LOCK 等待：大範圍掃描導致鎖升級，阻塞寫入
現象
壓測時單條 UPDATE 卡住整個系統：
UPDATE DD_ShenHe SET ZF = 0 WHERE ZF IS NULL;
表 400 萬列，ZF IS NULL 8000 列，但無索引 → Clustered Index Scan → 意圖鎖升級為表鎖 → 阻塞其他 Session。

解決
CREATE INDEX ind_dd_shenhe_zf ON DD_ShenHe(ZF) WHERE ZF IS NULL; -- 過濾索引更精準
索引建立後，系統立即恢復正常。

四、診斷工具箱速查

    
等待類型
常見原因
核心 DMV
典型對策
    
MEMORY
大型排序/雜湊、缺索引掃描
dm_exec_query_memory_grants
補索引、調整 MAXDOP、Resource Governor
    
LATCH_*
熱頁競爭、PFS/GAM/SGAM 爭用
dm_os_latch_stats, dm_exec_requests
分區表、填充因子、補索引減少掃描
    
LCK_*
長交易、大範圍掃描、鎖升級
dm_tran_locks, dm_exec_requests
縮短交易、補索引、ROWLOCK/NOLOCK 提示


核心心法：大多數等待最終歸因於「缺乏合適索引導致過度掃描」。養成「先看等待、再找 SQL、再補索引」的診斷循環，是 DBA 進階的關鍵。