最適化された入れ子ループやバッチソートを使用するクエリで、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 年