待機統計について説明する

  • 8 分

サーバーのパフォーマンスを監視するための包括的なアプローチには、サーバーが待機している内容の評価が含まれます。 待機統計は複雑であり、SQL Server には、実行中の各スレッドを監視し、スレッドが待機している内容をログに記録する数百の待機の種類が備わっています。

SQL Server のパフォーマンスの問題を効果的に検出してトラブルシューティングするには、待機統計のしくみと、要求の処理中にデータベース エンジンがそれらをどのように利用するかを理解することが不可欠です。 この知識により、ボトルネックを特定し、パフォーマンスをより正確に最適化できます。

待機の統計のしくみのスクリーンショット。

待機統計は、リソース待機、キュー待機、外部待機という 3 種類の待機に分類されます。

  • リソース待機は、SQL Server のワーカー スレッドで、現在スレッドによって使用されているリソースへのアクセスが要求されたときに発生します。 リソース待機の例は、ロック、ラッチ、ディスクI/O待機です。
  • キュー待機は、ワーカー スレッドがアイドル状態で作業の割り当てを待っているときに発生します。 キュー待機の例は、デッドロック監視や削除されたレコードのクリーンアップなどです。
  • 外部待機は、SQL Server がリンク サーバー クエリの完了のような外部プロセスを待機しているときに発生します。 外部待機の例は、クライアント アプリケーションに大きな結果セットが返されることに伴うネットワーク待機です。

sys.dm_os_wait_stats システム ビューを確認すると、実行されたスレッドによって検出されたすべての待機を調べることができます。Azure SQL Database の場合は、sys.dm_db_wait_stats を確認します。 sys.dm_exec_session_wait_stats システム ビューでは、アクティブな待機セッションが一覧表示されます。

これらのシステム ビューを使用すると、サーバーのパフォーマンスの概要を確認し、構成またはハードウェアの問題を簡単に特定できます。 このデータはインスタンスが開始した時点から保持されますが、変化を特定するために必要に応じてデータを消去できます。

待機統計は、サーバーでの合計待機時間に対する割合として評価されます。

割合ごとの待機の上位 10 のスクリーンショット。

sys.dm_os_wait_stats からのこのクエリの結果には、待機の種類と、各待機の種類に対する待機時間の割合の集計 (Wait Percentage 列) および平均待機時間 (秒単位) が示されます。

この例の場合、待機の種類 REDO_THREAD_PENDING_WORK と PARALLEL_REDO_TRAN_TURN によって示されているように、サーバーには Always On 可用性グループが配置されています。 待機 CXPACKET と SOS_SCHEDULER_YIELD の割合が比較的高いことから、このサーバーには CPU 負荷がかかっていることがわかります。

DMV は前回の SQL Server 起動時以降に累積された最も時間の長い待機の種類の一覧を提供するため、待機統計データを定期的に収集して保存しておくと、パフォーマンスの問題を理解し、他のデータベース イベントと関連付けるのに役立つ場合があります。

DMV は前回の SQL Server 起動時以降に累積された最も時間の長い待機の種類の一覧が提供するので、待機統計を定期的に収集して保存しておくと、パフォーマンスの問題を理解し、他のデータベース イベントと関連付けるのに役立つ場合があります。

SQL Server で使用できる待機にはいくつかの種類がありますが、その一部が一般的に使用されます。

  • RESOURCE_SEMAPHORE — 多くの場合、特定のクエリに対する過剰なメモリ許可が原因で、クエリがメモリが使用可能になるのを待機していることを示します。 この問題は通常、クエリの実行時間が長くなったり、タイムアウトしたりする形で現れます。 これらの待機の種類の原因には、古い統計、インデックスの不足、クエリのコンカレンシーの高さなどがあります。

  • LCK_M_X — 多くの場合、ブロックの問題を示します。 この問題は、 READ COMMITTED SNAPSHOT 分離レベルに変更するか、インデックス作成を最適化してトランザクション時間を短縮するか、T-SQL コード内のトランザクション管理を改善することで解決できます。

  • PAGEIOLATCH_SH — この待機の種類は、インデックスに関する問題や有用なインデックスが存在しないことを示す可能性があります。これにより、SQL Server は大量のデータをスキャンします。 または、待機数が少ないのに待機時間が長い場合は、ストレージのパフォーマンスの問題が示唆される可能性があります。 この動作を観察するには、waiting_tasks_count システム ビューのwait_time_ms列とsys.dm_os_wait_stats列のデータを分析して、特定の待機の種類の平均待機時間を計算します。

  • SOS_SCHEDULER_YIELD — この待機の種類は、CPU の使用率が高いことを示している可能性があります。これは、大きいスキャンの数が多いかインデックスが不足していることに関連し、多くの場合は CXPACKET 待機の数が多いことにも関連しています。

  • CXPACKET—この待機タイプの頻度が高い場合は、不適切な構成を示している可能性があります。 SQL Server 2019 より前では、 並列処理の最大限度 (MAXDOP) の既定の 設定では、クエリに使用可能なすべての CPU が使用されていました。 さらに、並列処理のコストしきい値が 5 に設定され、小さなクエリが並列で実行され、スループットが制限される可能性があります。 この待機の種類を減らすには、MAXDOP 設定を下げ、並列処理のコストしきい値を増やします。 ただし、 CXPACKET 待機の種類は、高い CPU 使用率を示すこともできます。これは通常、インデックスのチューニングによって解決されます。

  • PAGEIOLATCH_UP— データ ページ 2:1:1 のこの待機の種類は、ページ空き領域 (PFS) データ ページでの TempDB の競合を示すことができます。 各データ ファイルには、約 64 MB のデータごとに 1 つの PFS ページがあります。 この待機は通常、TempDB ファイルが 1 つだけの場合に発生します。SQL Server 2016 より前のように、既定の動作では TempDB に 1 つのデータ ファイルが使用されていました。 TempDB のベスト プラクティスは、CPU コアごとに 1 つのファイル (最大 8 個のファイル) を使用することです。 また、TempDB のデータ ファイルを同じサイズにし、同じ自動拡張の設定を使用して、それらが均等に使用されるようにすることも重要です。 SQL Server 2016 以降では、TempDB のデータ ファイルの拡張が制御され、整合性を保って同時に拡張されます。

クエリ ストア では、前述の DMV に加えて、特定のクエリに関連付けられている待機も追跡します。 クエリ ストアによって追跡される待機データは、DMV 内のデータほど詳細ではありませんが、クエリが待機している内容の概要が役立ちます。