待機統計について説明する
サーバーのパフォーマンスを監視する包括的な方法の 1 つは、サーバーが待機しているものを評価することです。 待機統計は複雑で、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 システム ビューでは、アクティブな待機セッションが一覧表示されます。
これらのシステム ビューを使用することで、DBA はサーバーのパフォーマンスの概要を把握し、構成またはハードウェアの問題を簡単に特定できます。 このデータはインスタンスが開始した時点から保持されますが、変化を特定するために必要に応じてデータを消去できます。
待機統計は、サーバーでの合計待機時間に対する割合として評価されます。
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 でスキャンされるデータが多くなりすぎます。 または、待機回数が少ないのに、待機時間が長い場合は、ストレージのパフォーマンスの問題を示している可能性があります。
sys.dm_os_wait_statsシステム ビューの waiting_tasks_count 列と wait_time_ms 列のデータを分析し、特定の待機の種類の平均待機時間を計算することにより、この動作を観察できます。SOS_SCHEDULER_YIELD — この待機の種類は、CPU の使用率が高いことを示している可能性があります。これは、大きいスキャンの数が多いかインデックスが不足していることに関連し、多くの場合は CXPACKET 待機の数が多いことにも関連しています。
CXPACKET — この待機の種類が多い場合は、不適切な構成を示している可能性があります。 SQL Server 2019 より前の場合、並列処理の最大限度の既定の設定では、クエリに使用可能なすべての CPU が使用されます。 また、並列処理のコストしきい値の設定の既定値は 5 であり、これにより小さいクエリが並列で実行され、スループットが制限される可能性があります。 MAXDOP を小さくして、並列処理のコストしきい値を増やすことにより、この待機の種類を減らすことはできますが、CXPACKET の待機の種類は高い CPU 使用率を示している可能性もあります。これは、通常、インデックスのチューニングによって解決されます。
PAGEIOLATCH_UP — データ ページ 2:1:1 のこの待機の種類は、ページ空き領域 (PFS) データ ページでの TempDB の競合を示している可能性があります。 各データ ファイルには、約 64 MB のデータごとに 1 つの PFS ページがあります。 この待機は、通常、TempDB ファイルが 1 つしかないことが原因で発生します。これは、SQL Server 2016 の前の既定の動作では、TempDB に 1 つのデータ ファイルが使用されていたためです。 ベスト プラクティスは、最大 8 ファイルまで、CPU コアごとに 1 つのファイルを使用することです。 また、TempDB のデータ ファイルを同じサイズにし、同じ自動拡張の設定を使用して、それらが均等に使用されるようにすることも重要です。 SQL Server 2016 以降では、TempDB のデータ ファイルの拡張が制御され、整合性を保って同時に拡張されます。
前に説明した DMV に加えて、クエリ ストアでも特定のクエリに関連する待機が追跡されます。 ただし、クエリ ストアによって追跡される待機データは、DMV のデータと同じ細分性では追跡されませんが、クエリが待機している内容についての適切な概要を提供できます。