リソース ガバナーのトラブルシューティング

分類に使用できる分類子 UDF がない場合、すべてのセッションは自動的に既定のワークロード グループに移動します。分類子 UDF を作成した後は、分類子 UDF がリソース ガバナーに登録されていること、およびメモリ内の構成が更新されていることを確認する必要があります。

分類子 UDF の作成、登録、および有効化は、次の 3 つの手順から構成されます。

• 1 番目の手順として、関数を作成します。

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  
  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• 2 番目の手順として、関数をリソース ガバナーに登録します。

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  
  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• 3 番目の手順として、リソース ガバナーのメモリ内の構成を更新します。

  ALTER RESOURCE GOVERNOR RECONFIGURE
  
  ALTER RESOURCE GOVERNOR RECONFIGURE
  

分類のトラブルシューティングを行う場合、最初に、作成した関数がリソース ガバナーに登録されていること、および構成が更新されていることを確認する必要があります。次のクエリを使用して、スキーマ名 (schema_name) とリソース ガバナーが現在使用している分類子 UDF の分類子関数名 (function_name) を取得します。

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

前の方法は、分類子 UDF を変更したにもかかわらずセッションを分類するためにリソース ガバナーで以前の関数ロジックが使用される状況のトラブルシューティングに使用できます。この動作は、変更がメモリ内の構成に反映されていないことを示しています。

作成した分類子関数が非常に複雑なため、期待される結果が得られない場合や、リソースが大量に消費される場合があります。基本的なトラブルシューティングを行った後は、関数ロジックが適切であることを確認する必要があります。最悪のシナリオは、コーディングの不具合が原因で無限ループやランナウェイ クエリが発生することです。

記述に問題のある分類子関数のトラブルシューティングには、専用管理者接続 (DAC) を使用できます。DAC は分類の対象とならず、実行中のリソース ガバナーで着信セッションが分類されている間も使用できるためです。詳細については、「専用管理者接続の使用」を参照してください。

注
トラブルシューティングに DAC を使用できない場合は、システムをシングル ユーザー モードで再起動します。シングル ユーザー モードは分類の対象となりませんが、実行中のリソース ガバナーによる分類を診断することはできません。

次の情報を照会して、分類子関数に関する情報を取得できます。

• sys.dm_exec_query_stats (実際の関数ではなく、ステートメントに関する情報が含まれています)。

• sys.dm_exec_sql_text (sys.dm_exec_query_stats から取得した sql_handle と組み合わせて使用します)。

• PreConnect:Starting イベント クラス (分類子関数の ID と名前を提供します)。

このシナリオでは、ユーザーから報告されたパフォーマンスの低下の原因として、要求数が調整されていることを疑います。

最初に行う必要があるのは、ユーザーが属しているグループに対して要求数の調整が明示的に構成されているかどうかを調べることです。そのためには、ユーザーのグループ メンバーシップを調べて、GROUP_MAX_REQUESTS 設定が有効になっているかどうかを確認します。GROUP_MAX_REQUESTS が有効ではない場合、要求数の調整は明示的に設定されていません。次の手順を使用してさらに調査を進める必要があります。

• sys.dm_os_waiting_tasks に対するクエリを実行して、RESMGR_THROTTLED の待機の種類で待機している要求があるかどうかを確認します。この待機の種類が存在する場合は、要求数の調整が行われていることを示します。

• パフォーマンス モニターを起動し、Queued requests カウンターと Active requests カウンターを使用してデータを収集します。0 以外の Queued request 値は、要求の調整を示します。

• Active requests 値が GROUP_MAX_REQUESTS の設定と一致するかどうかを確認します。Active requests 値が GROUP_MAX_REQUESTS の設定値を超える場合、このグループには調整できない要求 (開いているトランザクションなど) が存在する可能性があります。

• Queued requests 値が 0 の場合は、プールが大量の要求によって過負荷になっている可能性があるので、同じリソース プールを共有しているすべてのワークロード グループの Active requests 値を調べます。

リソース ガバナーのイベント生成によって適用されるポリシーを使用している場合は、最大 CPU 制限に到達したときに生成されるイベントを使用できます。

このシナリオでは、CPU を大量に使用しているクエリを検出するために構成されている最大 CPU 制限 (REQUEST_MAX_CPU_TIME_SEC) が小さすぎるかどうかを判定します。

CPU 制限の設定を確認するうえで、次の操作が有用です。

• SQL トレース セッションを起動し、CPU Threshold Exceeded イベントを収集します。ユーザー要求が最大 CPU 使用制限に達すると、サーバーは自動的に SQL トレース イベントを生成します。設定値が小さすぎる場合は、これらのイベントが大量に生成されます。

注
このイベントはサーバー イベント通知としても公開されるので、このイベントに応答するスクリプトを作成できます。

• パフォーマンス モニターを起動し、Max request cpu time (ms) カウンターを使用してデータを収集します。このカウンターの値は、ワークロード グループに適切な制限値を設定するためのガイドとして使用できます。

このシナリオでは、リソース ガバナーの MAX_CPU_PERCENT 設定に達しているかまたは近い値になっている CPU usage % パフォーマンス カウンターから、CPU 帯域幅が調整されていることを疑います。次のクエリは、SQL Server インスタンスのすべてのワークロード グループおよびリソース プールの "CPU usage %" 値を返します。

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

詳細については、「sys.dm_os_performance_counters (Transact-SQL)」を参照してください。

システムに対して次の項目をチェックすることで、CPU 帯域幅が調整されているかどうかを判定できます。

• サーバーの CPU の合計使用率をチェックします。SQL Server 以外の負荷が現在アクティブである場合、トラブルシューティングしているクエリにも影響が及ぶ可能性があります。

• リソース プール間での CPU 使用率の配分をチェックします。リソース プールは、別のプールで CPU 使用率に構成されている最小値が大きい場合に調整されることがあります。カウンターを使用して、予想値 (計算された CPU 使用率) と実際の CPU 使用率とを比較します。

• 問題のリソース プールに割り当てられているワークロード グループをチェックします。他のワークロード グループの負荷により、同じプールを共有しているユーザーに影響が及ぶ可能性があります。

• スケジューラ間での CPU 使用率の配分をチェックします。調査しているクエリが、実行時間が長いクエリを含むスケジューラに配置されている可能性があります。その場合、クエリが調整されているように見えますが、実際の問題は、スケジューラ間で負荷が不均等に配分されていることにあります。

• リソース ガバナーの設定で調整されているのではなく、他のセッションによってワークロードがブロックされている可能性をチェックします。

• 現在システムでクエリを実行しているセッションの数をチェックします。同時実行要求の数が増えると、CPU 不足が発生するのを防ぐために、SQL Server ではすべての要求に最低限の CPU 時間を割り当てようとします。

このシナリオでは、クエリの実行速度が遅い原因として、許可メモリのサイズを疑います。

リソース ガバナーは、大規模なクエリが制限内に収まるように、許可メモリを小さくすることで最大クエリ メモリ制限を適用します。クエリに設定されたメモリ許可が 100% を下回る場合は、ディスクへの一時データの書き込みが必要になり、パフォーマンスに大きな影響が及ぶことがあります。

適切な最大クエリ サイズ制限を設定するには、大規模なクエリの割合を把握する必要があります。最適な設定を決定するうえで、次の操作が有用です。

• sys.dm_exec_query_memory_grants に対してクエリを実行して、現在のメモリ許可の状態を確認します。ideal_memory_kb 列には、基数の推定値に基づいた最適な量が示されます。requested_memory_kb 列には、要求量が示されます。この値は、最大クエリ制限に達した後に削減されている場合もあります。requested_memory_kb が ideal_memory_kb と比べて著しく小さい場合、データの書き込みが頻繁に発生する可能性があります (基数の推定値が正しい場合)。

• パフォーマンス モニターを起動し、Reduced memory grants/sec カウンターを使用してデータを収集します。このカウンターの値は、最大要求サイズ制限に達した後に受け取った、最適な量を下回るメモリ許可数の割合を表します。最適な量のメモリ許可と比べた場合、大規模なクエリでは、メモリ制限を守るためにディスクへの書き込みが必要になるので、実行速度がより遅くなる可能性があります。

メモリ許可の問題を軽減するには、場合によってプール サイズ制限や最大メモリ サイズ制限の値を大きくする必要があります。

注
最大メモリ サイズの値のみを大きくすると、大規模なクエリの同時実行性が低下する場合があります。

このシナリオでは、クエリがメモリ許可タイムアウト エラーで失敗します。

メモリ許可タイムアウトには、アクティブなメモリ許可要求の合計数と、ワークロード グループおよびリソース プールの定義に指定されたメモリ制限の両方が影響します。単独のリソース プールが複数のリソース グループで共有される場合は、他のグループ内の同時実行クエリの数もメモリ許可タイムアウトに影響を与えます。

最適なリソース プールの設定を決定するうえで、次の操作が有用です。

• sys.dm_exec_query_memory_grants に対してクエリを実行して、このグループおよびプールのメモリ許可および待機クエリの数を確認します。

• sys.dm_exec_query_resource_semaphores に対してクエリを実行して、許可されているメモリの合計と目標量を確認します。

許可メモリの使用量が使用可能なメモリ空間を超える場合は、リソース プール サイズ制限値を大きくします。

クエリがメモリ不足エラーで失敗します。

基本的なトラブルシューティング

高度なトラブルシューティング

メモリ不足 (701) エラーは、タスクがメモリ マネージャーからメモリ ブロックを割り当てようとしたときにその試みが失敗すると返される一般的なエラーです。詳細については、「MSSQLSERVER_701」を参照してください。

次の条件が満たされる場合、このエラーが発生することがあります。

• メモリ プールがその合計制限値に達した。

注
リソース ガバナーがこの条件の唯一の原因とは限りません。サーバー上でメモリ要求が大きい他のアプリケーションが実行されていることにより、この条件が満たされる場合があります。

• 必要な量を予約するのに十分な空きブロックが仮想アドレス空間にないため、複数ページまたは仮想アドレス空間の割り当てが失敗する。この状況は、32 ビット アーキテクチャ上で発生し、64 ビット アーキテクチャ上で発生する可能性はほとんどありません。

• コミットメントの合計がコミット制限に達したため、複数ページまたは仮想アドレス空間の割り当てが失敗する。この状況は、32 ビット アーキテクチャと 64 ビット アーキテクチャの両方で発生します。

メモリ不足エラーが発生した場合は、エラー ログが、エラーの調査を開始する場所として最適です。ログには、次の例のような出力が含まれています。

2006-01-28 04:27:15.43 spid51 Failed allocate pages: FAIL_PAGE_ALLOCATION 1

エラー ログに記録されるエラーには、次のエラーがあります。

• FAIL_PAGE_ALLOCATION というメッセージと、割り当てようとしたページの数

• FAIL_VIRTUAL_RESERVE というメッセージと、予約しようとしたバイト数

• FAIL_VIRTUAL_COMMIT というメッセージと、コミットしようとしたバイト数

メモリ不足エラーを発生させたタスクが必ずしもエラーの原因を作っているタスクとは限らないことに注意してください。ランナウェイ タスクが存在する場合を除き、一般にメモリ不足状態は、複数のタスクが実行されている結果としての状態です。その結果、たとえば一般的な FAIL_PAGE_ALLOCATION エラーの場合、システムの使用状況について幅広い範囲で調査する必要があります。

エラー ログ内で次に有用な情報は、メモリ状態の出力です。エラーに応じて、個々のメモリ クラークの単一ページ、複数ページ、仮想の予約数またはコミット数を確認する必要があります。最大のメモリ コンシューマーを識別することが、エラーの調査を続行するうえで重要です。一般的な最大メモリ コンシューマーの種類を次に示します。

• MEMORYCLERK_* は、サーバー構成またはワークロードで特定のメモリ割り当てが必要であることを示します。SQL Server コンポーネントには対応するメモリ クラークがあり、個々のコンポーネントは複数のメモリ クラークを持つことができます。詳細については、「sys.dm_os_memory_clerks (Transact-SQL)」を参照してください。問題の原因となるワークロードをメモリ クラークから識別できることもありますが、通常は、メモリの大量消費を引き起こしている原因を特定するために、クラークに関連付けられているメモリ オブジェクトを調査することになります。

• CACHESTORE_*、USERSTORE_*、OBJECTSTORE_* は、キャッシュの種類です。キャッシュによるメモリ消費量が多いことは、次の状況を意味している可能性があります。

  • メモリがキャッシュの外部で割り当てられているにもかかわらず、削除できるエントリとして挿入されていない。これは、前の MEMORYCLERK のケースに似ています。

  • すべてのキャッシュ エントリが使用中のため、削除できない。この状態は、sys.dm_os_memory_cache_counters をチェックして entries_count 列と entries_in_use_count 列の値を比較することで確認できます。

• MEMORYCLERK_SQLQERESERVATIONS は、並べ替え/結合を使用するクエリを実行するためにクエリの実行 (QE) によって予約されたメモリの量を示します。一般に、メモリの予約量が多いときに発生するメモリ不足エラーは、サーバーのバグを示します。

さらに、エラー ログのメモリ状態の出力を見ると、どのメモリ プールが使い果たされているか判別できます。すべてのプールのメモリ ブローカーは、盗用されたメモリ (コンパイル)、キャッシュされたメモリ、および予約された (許可された) メモリの間のメモリ配分を示します。3 つのブローカーの数値は、メモリ クラークに関連付けられた、先行するメモリ オブジェクトに対応します。カスタム スクリプトを使用して完全なダンプから情報を抽出することで、プールに対して特定のクラークまたはメモリ オブジェクトから割り当てられたメモリの量を調べることができます。さらに、sys.dm_os_memory_cache_entries 動的管理ビューには、各エントリが関連付けられている pool_id が示されます。

カスタマー サポート サービスへのお問い合わせの際には、次の情報を収集してサポート スタッフにご提供ください。

• メモリ不足エラーを示すエラー ログと、エラー発生時のメモリ状態の出力。

• 次のステートメントからの出力。

  dbcc memorystatus
  dbcc sqlperf(spinlockstats)
  select * from sys.dm_os_memory_clerks
  select * from sys.dm_os_wait_stats order by wait_type
  select * from sys.dm_os_waiting_tasks
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
  select * from sys.dm_os_memory_cache_clock_hands
  
  dbcc memorystatus
  dbcc sqlperf(spinlockstats)
  select * from sys.dm_os_memory_clerks
  select * from sys.dm_os_wait_stats order by wait_type
  select * from sys.dm_os_waiting_tasks
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
  select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
  select * from sys.dm_os_memory_cache_clock_hands
  

• 必要に応じて、T8004 を使用して収集したメモリ不足ダンプ。このミニダンプには、リング バッファーやスピンロック/待機状態などの有用な情報が含まれます。T8004 のダンプ カウンターは、T8004 をいったんオフにしてからオンにしてサーバーを再起動する必要なく、リセットできます。

このシナリオでは、クエリの実行速度が遅い原因として、最適化されていないクエリ プランを疑います。クエリ オプティマイザーは、リソース プールのメモリ制限設定が低いために十分なメモリを確保できないと、最適化されていないクエリ プランを生成する場合があります。

最適なリソース プールの設定を決定するうえで、次の操作が有用です。

• Query optimizations/sec カウンターのデータを取得して、ワークロード グループに大量のクエリ コンパイルが存在するかどうかを確認します。

• Suboptimal plans/sec カウンターのデータを取得して、クエリ オプティマイザーによって最適化されていないプランが生成されることが多いかどうかを確認します。

どちらかの条件が満たされる場合は、リソース プールのメモリ制限値を大きくすることを検討してください。