使用優化巢狀迴圈或批次排序的查詢可能會發生高 CPU 或記憶體授與
本文可協助您解決在 SQL Server 中執行查詢時發生高 CPU 使用量的問題。
適用于:SQL Server
原始 KB 編號: 2009160
徵狀
當您操作具有高度並行工作負載的 Microsoft SQL Server 時,您可能會注意到查詢中有一些效能問題。 此行為可能會顯示為中至高CPU使用量或極端記憶體授與要求。
您也可能遇到其他副作用,例如 OOM 條件、計劃快取收回的記憶體壓力,或非預期 RESOURCE_SEMAPHORE
的等候。
此外,您可能會注意到耗用大量CPU或記憶體之查詢的查詢計劃,已將巢狀循環聯結運算子的 OPTIMIZED 屬性設定為 True。
原因
在某些情況下,SQL Server 查詢處理器會導入選擇性排序作業來改善效能,可能會發生此問題。 這項作業稱為「優化巢狀循環」或「批次排序」,查詢優化器會決定何時最適合導入這些運算符。 在極少數情況下,查詢只會觸碰幾個數據列,但排序作業的設定成本非常龐大,因此優化巢狀迴圈的成本超過其優點。 因此,在這些情況下,您可能會發現效能比預期的效能慢。
解決方案
追蹤旗標 2340
若要修正此問題,請使用追蹤旗標 2340 來停用優化。 追蹤旗標 2340 指示查詢處理器在產生查詢計劃時,不要在批次排序 () 使用排序作業) 優化巢狀循環聯結。 這會影響整個實例。
啟用此追蹤旗標之前,您可以徹底測試應用程式,以確保在停用此優化時獲得預期的效能優勢。 這是因為當計劃接觸到的數據列數目大幅增加時,排序優化會很有説明。
如需詳細資訊,請 參閱 DBCC TRACEON - 追蹤旗標 (Transact-SQL) 。
修改程序代碼以使用DISABLE_OPTIMIZED_NESTED_LOOP提示
或者,套用下列 DISABLE_OPTIMIZED_NESTED_LOOP
查詢提示,以在查詢層級停用優化。
SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT (DISABLE_OPTIMIZED_NESTED_LOOP));
如需詳細資訊,請 參閱 DISABLE_OPTIMIZED_NESTED_LOOP。
其他相關資訊
適用於
- SQL Server 2005 到 SQL Server 2019 年