SQL Serverの期待値の統計情報、またはどこが痛いのか教えてください

SQL Serverのパフォーマンスの問題を何回経験し、どこを調べるかを決めましたか？



めったに使用されないSQL Serverパフォーマンスのトラブルシューティング方法の1つは、期待値とキュー（期待値統計とも呼ばれます）と呼ばれます。 方法論の基本的な前提は、SQL Serverがどのスレッドを待機するかを常に監視することです。 パフォーマンスの問題の考えられる原因のリストを短縮するために、SQL Serverにこの情報を要求できます。 期待はSQL Serverが監視するものです。 キューは、スレッドがアクセスを待機しているリソースです。 通常、システムは膨大な数の期待を獲得し、それらはすべてさまざまなリソースへのアクセスを待機することを意味します。 たとえば、PAGEIOLATCH_EXを待つことは、ストリームがディスクからバッファプールへのデータページの読み取りを待っていることを意味します。 LCK_M_Xを待っているということは、スレッドが何かに排他ロックをかける機会を待っていることを意味します。



素晴らしいニュースは、SQL Server がパフォーマンスの問題が何であるかを正確に知っていることです。あなたがする必要があるのは、彼に尋ねるだけです...そして、彼が言うことを正しく解釈することはもう少し複雑になります。



次の情報は、すべての期待を心配し、その原因を理解している人向けです。 期待は常に生じます。 これが、SQL Serverの作業スケジューリングシステムの仕組みです。



スレッドはプロセッサを使用し、リソースへのアクセスを待機する必要に直面するまでステータスが「実行中」になります。 この場合、一時停止状態（SUSPENDED）のスレッドの順序なしリストに配置されます。 同時に、FIFOの原則（先入れ先出し）に従って編成され、ステータスが「実行可能」（RUNNABLE）であるプロセッサへのアクセスを待機しているスレッドのキュー内の次のスレッドは、プロセッサへのアクセスを取得して「実行中」になります。 「一時停止」状態のスレッドが、そのリソースが使用可能であるという通知を受け取ると、「実行準備完了」になり、実行準備が整ったスレッドのキューの最後に配置されます。 フローは、タスクが完了するまで、チェーン「実行中」-「一時停止中」-「実行準備完了」に沿って周期的な動きを続けます。 動的管理ビュー（DMV）sys.dm_exec_requestsを使用して、プロセスとそのステータスを確認できます。



SQL Serverは、スレッドが実行状態を終了してからその状態に戻るまでの経過時間を監視し、それを「待機時間」として定義し、準備完了状態で費やした時間を「時間」として定義します。信号待機時間」、つまり プロセッサにアクセスするためにリソースの可用性に関するシグナルを受信した後、スレッドが必要とする時間。 合計タイムアウトから信号タイムアウトを引くことにより、スレッドが「リソース待機時間」と呼ばれる「一時停止」状態で費やす時間を理解する必要があります。



これについて読むことをお勧めする優れた情報源は、待機統計に関する新しい（2014年）文書「期待値統計を使用したSQL Serverパフォーマンスの調整：初心者向けガイド」 （英語）です。 また、はるかに古いドキュメント（ 期待とキューを使用したパフォーマンス管理 （英語）には多くの有用な情報がありますが、現時点ではかなり時代遅れです。さまざまなタイプの期待（および短期ロックのクラス）の最良のチュートリアルは、 期待の包括的なライブラリです） （英語）および短期ロック （英語）。



DMV sys.dm_os_wait_statsを使用して、累積待機統計についてSQL Serverを照会できます。 多くの人は、DMV呼び出しを何らかの要約コードでラップすることを好みます。 以下は、2016年時点での私のスクリプトの最新バージョンです。これはすべてのバージョンで動作し、SQL Server 2016の待機タイプが含まれています（Azure で使用するスクリプトのバージョンはこちらをご覧ください ）。

WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type] NOT IN ( N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', -- Maybe uncomment these four if you have mirroring issues N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', -- Maybe uncomment these six if you have AG issues N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_XE_GETTARGETSTATE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_RECOVERY', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') AND [waiting_tasks_count] > 0 ) SELECT MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S], CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL] FROM [Waits] AS [W1] INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum] GROUP BY [W1].[RowNum] HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリ結果には、システム内のすべての期待値の割合でグループ化された期待値が降順で表示されます。 （潜在的に）注意を払う価値がある期待はリストの一番上にあり、SQL Serverが時間を費やすほとんどの期待を表しています。 考慮から除外された期待値の大きなリストが表示されます。前述したように、それらは常に発生し、上にリストされたものは通常無視できます。



このコードを使用して、サーバーによって蓄積された統計をリセットすることもできます。

 DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR); GO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そしてもちろん、結果を数時間ごとまたは毎日保存し、時間分析を行って変更の方向を把握したり、問題が発生し始めた場合に自動的に問題を追跡したりすることができます。

パフォーマンスダッシュボードを使用して、SQL Server 2005の結果とSQL Server 2008のデータコレクタをグラフィカルに表示することもできます。SQLServer 2000では、DBCC SQLPERF（N'waitstats '）を使用できます。



結果が得られたら、それらをどのように解釈し、どこを見るかについて考え始めるでしょう。 以前に参照したドキュメントには、ほとんどのタイプの期待に関する豊富な情報があります（SQL Server 2008で追加されたものを除く）。



ここで、少し前に公開した研究の結果を提供したいと思います。 上記のコードを実行して結果を知らせてください。 1823台のサーバーから大量の結果が出ました-ありがとう！



以下は、結果のグラフ表示です。







クライアントシステムで何度も見た結果、トップ4の結果にはまったく驚きません。



私の記事の続きでは、調査回答者が提供する最も人気のあるタイプの期待値を降順でリストし、それらがあなたのシステムの基本である場合に正確に何を意味できるかをいくつかの言葉でコメントします。 リスト形式は、指定されたタイプの待機が主なものである回答者からのシステムの数を示します。

• 505：CXPACKET
  
  同時実行を意味しますが、必ずしも問題ではありません。 並列リクエストのコーディネータースレッドは、常にこれらの期待値を蓄積します。 並列スレッドがビジーでないか、スレッドの1つがブロックされている場合、待機中のスレッドもCXPACKET待機を蓄積します。これにより、このタイプの統計の蓄積が速くなります-これが問題です。 1つのスレッドは他のスレッドよりも多くの作業を行う場合があるため、長いスレッドが作業を完了するまでリクエスト全体がブロックされます。 このタイプの待機が大きな待機桁PAGEIOLATCH_XXと組み合わされている場合、これは不正確な非クラスター化インデックスまたは不適切なクエリ実行プランのために、大きなテーブルのスキャンである可能性があります。 これが理由でない場合は、問題のあるクエリまたはサーバーインスタンス全体に対して、4、2、または1の値でMAXDOPオプションを使用してみてください（「max degree of parallelism」パラメーターでサーバーに設定されます）。 システムがNUMA回路に基づいている場合は、MAXDOPを単一のNUMAノードのプロセッサ数に設定して、これが問題であるかどうかを判断してください。 また、混在負荷システムにMAXDOPをインストールした場合の影響を判断する必要もあります。 正直に言うと、インスタンス全体のMAXDOP値を下げる前に、「並列処理のコストしきい値」パラメーターを試してみます（最初は25に上げます）。 また、SQL Server 2008のエンタープライズバージョンのリソースガバナーを忘れないでください。これにより、サーバー接続の特定のグループにプロセッサ数を設定できます。
• 304：PAGEIOLATCH_XX
  
  これは、SQL Serverがディスクからメモリへのデータページの読み取りを待機する場所です。 このタイプの待機は、I / Oシステムの問題（このタイプの待機に対する最初の反応）を示している可能性がありますが、なぜI / Oシステムは非常に多くの読み取りを処理する必要がありますか？ おそらく、バッファプール/メモリ（通常の負荷に十分なメモリではない）、実行計画の突然の変更、および検索ではなく大規模な並列スキャンにつながる圧力、プランキャッシュの肥大化、またはその他の理由によって圧力がかけられています。 入力/出力システムの主な問題を想定しないでください。
• 275：ASYNC_NETWORK_IO
  
  ここで、SQL Serverはクライアントがデータの受信を完了するのを待ちます。 その理由は、クライアントが大量のデータを要求したか、コードが悪いためにデータを受信するのが遅すぎるためかもしれません。ネットワークで問題を見たことはほとんどありません。 クライアントは、多くの場合、クライアントにデータをキャッシュし、すぐに読み取りを終了するようSQL Serverに通知する代わりに、一度に1行（いわゆるRBARまたはRow-By-Agonizing-Row）を読み取ります。
• 112：WRITELOG
  
  ログ管理サブシステムは、ログがディスクに書き込まれるのを待っています。 原則として、入力/入力システムはログのボリューム全体のタイムリーな記録を提供できないことを意味しますが、高負荷のシステムでは、ログ記録の一般的な制限が原因である可能性があり、複数のデータベース間で負荷を共有するか、トランザクションを作成する必要がある場合がありますディスクへのログエントリの数を減らすために少し長くなります。 原因がI / Oシステムにあることを確認するには、DMV sys.dm_io_virtual_file_statsを使用してログファイルのI / O遅延を調べ、WRITELOG遅延時間と一致するかどうかを確認します。 WRITELOGの持続時間が長い場合は、ディスクへの書き込みに関して内部競合が発生しているため、負荷を分担する必要があります。 そうでない場合は、なぜこのような大きなトランザクションログを作成するのかを調べてください。 ここ （英語）およびここ （英語）いくつかのアイデアを収集できます。
  
  （翻訳者のメモ：次のクエリを使用すると、サーバー上の各データベースの各ファイルのI / O遅延に関する統計を簡単かつ便利な方法で取得できます。
  
  
  非表示のテキスト

   -- : .   > 20  USE master GO SELECT cast(db_name(a.database_id) AS VARCHAR) AS Database_Name , b.physical_name --, a.io_stall , a.size_on_disk_bytes , a.io_stall_read_ms / a.num_of_reads '.   ' , a.io_stall_write_ms / a.num_of_writes '.   ' --, * FROM sys.dm_io_virtual_file_stats(NULL, NULL) a INNER JOIN sys.master_files b ON a.database_id = b.database_id AND a.file_id = b.file_id where num_of_writes > 0 and num_of_reads > 0 ORDER BY Database_Name , a.io_stall DESC
        
        
  
          
          
          
        
  
      
          
          
          
        
        
  
          
          
          
        
  
      
       

• 109：BROKER_RECEIVE_WAITFOR
  
  ここで、Service Brokerは新しいメッセージを待っています。 この期待値を除外リストに追加し、待機統計を使用してクエリを再実行することをお勧めします。
• 086：MSQL_XP
  
  ここで、SQL Serverは拡張ストアドプロシージャの実行を待ちます。 これは、拡張ストアドプロシージャのコードに問題がある可能性があります。
• 074：OLEDB
  
  名前が示すように、これはOLEDBを使用した相互作用の期待です-たとえば、リンクサーバーと。 ただし、OLEDBはDMVおよびDBCC CHECKDBチームでも使用されているため、リンクサーバーに問題があるとは思わないでください。DMV呼び出しを過剰に使用する外部監視システムである可能性があります。 これが実際にリンクサーバーである場合は、リンクサーバーの期待値を分析し、パフォーマンスの問題を特定します。
• 054：バックアップ
  
  テープに直接バックアップするときに表示されます。これは非常に遅いです。 私はむしろこの期待を除外します。 （翻訳者のメモ：ディスクにバックアップを書き込むときにこのタイプの期待に応えましたが、小さなデータベースのバックアップには非常に長い時間がかかり、技術的な中断中に実行する時間がなく、ユーザーにパフォーマンスの問題を引き起こしました。これが入力システムである可能性があります/バックアップに使用される出力は、生産性を高める可能性を検討するか、サービスプランを修正する必要があります（短い技術的な中断で完全バックアップを完了せず、差分バックアップに置き換えてください）
• 041：LCK_M_XX
  
  ここでは、スレッドは単にオブジェクトのロックへのアクセスを待機し、ロックの問題を意味します。 これは、不要なロックのエスカレーションまたは不正なコードによって引き起こされる可能性がありますが、I / O操作に時間がかかりすぎて、通常よりも長くロックを保持することによっても引き起こされる可能性があります。 DMV sys.dm_os_waiting_tasksを使用してロック関連のリソースを確認します。 主な問題がロックであると想定しないでください。
• 032：ONDEMAND_TASK_QUEUE
  
  これは正常であり、バックグラウンドタスクシステムの一部です（遅延リセット、バックグラウンドクリーンアップなど）。 この期待を除外リストに追加し、待機統計を使用してクエリを再実行します。
• 031：バックアップバッファ
  
  テープに直接バックアップするときに表示されます。これは非常に遅いです。 私はむしろこの期待を除外します。
• 027：IO_COMPLETION
  
  SQL ServerはI / Oの完了を待機しており、このタイプの待機はI / Oシステムの問題の指標になる可能性があります。
• 024：SOS_SCHEDULER_YIELD
  
  ほとんどの場合、これは他のタイプの期待に該当しないコードですが、循環ロックの競合になる場合もあります。
• 022：DBMIRROR_EVENTS_QUEUE
  
  022：DBMIRRORING_CMD
  
  これら2つのタイプは、データベースミラーリングシステムが座って何かを待っていることを示しています。 これらの期待を除外リストに追加し、待機統計を使用してリクエストを再実行します。
• 018：PAGELATCH_XX
  
  これは、メモリ内のページのコピーにアクセスするための競争です。 最もよく知られているケースは、PFS、SGAM、およびGAMの競合です。これらは、特定の種類の負荷に対してtempdbデータベースで発生します 。 競合しているページを見つけるには、DMV sys.dm_os_waiting_tasksを使用して、どのページがブロックを引き起こしているかを調べる必要があります。 tempdbデータベースの問題に関して、Robert Davis（彼のブログ 、 twitter ）はそれらを解決する方法を示す良い記事を書きました 。私が見たもう1つの一般的な理由は、シリアルキー（IDENTITY）を使用したインデックスへの競合する挿入で頻繁に更新されるインデックスでした。
• 016：LATCH_XX
  
  これは、SQL Serverの非ページ構造に対する競合です。したがって、一般的なI / Oおよびデータとは関係ありません。 このタイプの遅延の理由を理解するのは難しい場合があり、DMV sys.dm_os_latch_statsを使用する必要があります。
• 013：PREEMPTIVE_OS_PIPEOPS
  
  ここで、SQL Serverはプロアクティブスケジューリングに切り替えて、Windowsに何かを要求します。 このタイプの待機は2008バージョンで追加されたもので、まだ文書化されていません。 それが何を意味するかを知る最も簡単な方法は、最初のPREEMPTIVE_OS_を削除し、MSDNに残っているものを探すことです-これはWindows APIの名前になります。
• 013：スレッドプール
  
  このタイプは、システムが要求を満たすのに十分なワークフローがないことを示しています。 通常、その理由は、多数の高度に並列化された要求が実行しようとしているためです。 （翻訳者のメモ：「max worker threads」サーバーパラメーターの意図的に切り捨てられた値になることもあります）
• 009：BROKER_TRANSMITTER
  
  ここで、Service Brokerは新しいメッセージが送信されるのを待っています。 この期待値を除外リストに追加し、待機統計を使用してクエリを再実行することをお勧めします。
• 006：SQLTRACE_WAIT_ENTRIES
  
  SQL Serverのトレース部分。 この期待値を除外リストに追加し、待機統計を使用してクエリを再実行することをお勧めします。
• 005：DBMIRROR_DBM_MUTEX
  
  これは文書化されていないタイプの1つであり、データベースミラーリングセッション間で共有されるバッファーを送信するための競合があります。 ミラーリングセッションが多すぎることを意味する場合があります。
• 005：RESOURCE_SEMAPHORE
  
  ここでは、クエリはメモリの実行を待機します（ソートなどのクエリ演算子の処理に使用されるメモリ）。 これは、競合負荷でのメモリ不足の可能性があります。
• 003：PREEMPTIVE_OS_AUTHENTICATIONOPS
  
  003：PREEMPTIVE_OS_GENERICOPS
  
  ここで、SQL Serverはプロアクティブスケジューリングに切り替えて、Windowsに何かを要求します。 このタイプの待機は2008バージョンで追加されたもので、まだ文書化されていません。 それが何を意味するかを知る最も簡単な方法は、最初のPREEMPTIVE_OS_を削除し、MSDNに残っているものを探すことです-これはWindows APIの名前になります。
• 003：SLEEP_BPOOL_FLUSH
  
  この期待はしばしば見られ、I / Oシステムの過負荷を回避するためにブレークポイント自体が制限されることを意味します。 この期待値を除外リストに追加し、待機統計を使用してクエリを再実行することをお勧めします。
• 002：MSQL_DQ
  
  ここで、SQL Serverは分散クエリが実行されるのを待ちます。 これは、分散リクエストに問題があることを示している場合もありますが、単に標準的な場合もあります。
• 002：RESOURCE_SEMAPHORE_QUERY_COMPILE
  
  システム内のクエリの競合する再コンパイルが多すぎる場合、SQL Serverは実行を制限します。 制限のレベルは覚えていませんが、この期待は過剰な再コンパイル、またはおそらく、一度限りのプランの頻繁な使用を意味する場合があります。
• 001：DAC_INIT
  
  私はこれを前に見たことがなく、その理由は管理接続の初期化であるとBOLは言います。 これがどのように誰かのシステムの優先事項になるのか想像できません...
• 001：MSSEARCH
  
  このタイプは、フルテキスト操作では正常です。 これが先制的な期待である場合、システムがフルテキストクエリの実行に最も時間を費やしていることを意味する場合があります。 このタイプの待機を除外リストに追加することを検討してください。
• 001：PREEMPTIVE_OS_FILEOPS
  
  001：PREEMPTIVE_OS_LIBRARYOPS
  
  001：PREEMPTIVE_OS_LOOKUPACCOUNTSID
  
  001：PREEMPTIVE_OS_QUERYREGISTRY
  
  ここで、SQL Serverはプロアクティブスケジューリングに切り替えて、Windowsに何かを要求します。 このタイプの待機は2008バージョンで追加されたもので、まだ文書化されていません。 それが何を意味するかを知る最も簡単な方法は、最初のPREEMPTIVE_OS_を削除し、MSDNに残っているものを探すことです-これはWindows APIの名前になります。
• 001：SQLTRACE_LOCK
  
  SQL Serverのトレース部分。 この期待値を除外リストに追加し、待機統計を使用してクエリを再実行することをお勧めします。

おもしろかった！ 具体的に何かに興味があるか、この記事を読んで楽しんだか教えてください！