使用優化巢狀迴圈或批次排序的查詢可能會發生高 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。

其他相關資訊

Database Engine 服務啟動選項

適用於

  • SQL Server 2005 到 SQL Server 2019 年