SQL Serverのメモリ許可によって発生するパフォーマンスの低下またはメモリ不足の問題のトラブルシューティング
メモリ許可とは
クエリ実行 (QE) の予約、クエリ実行メモリ、ワークスペース メモリ、メモリ予約とも呼ばれるメモリ許可は、クエリ実行時のメモリ使用量を表します。 SQL Serverは、クエリの実行中に、次の 1 つ以上の目的でこのメモリを割り当てます。
- 並べ替え操作
- ハッシュ操作
- 一括コピー操作 (一般的な問題ではありません)
- ハッシュ ディクショナリ/テーブルは実行時にインデックス作成に使用されるため、COLUMNSTORE インデックスへの挿入を含むインデックスの作成 (一般的な問題ではありません)
コンテキストを提供するために、有効期間中に、クエリは、実行する必要がある内容に応じて、異なるメモリ アロケーターまたはクラークからメモリを要求する場合があります。 たとえば、クエリが最初に解析されてコンパイルされると、コンパイル メモリが消費されます。 クエリがコンパイルされると、そのメモリが解放され、結果のクエリ プランがプラン キャッシュ メモリに格納されます。 プランがキャッシュされると、クエリを実行する準備が整います。 クエリが並べ替え操作、ハッシュ一致操作 (JOIN または集計)、または COLUMNSTORE インデックスへの挿入を行う場合は、クエリ実行アロケーターからのメモリが使用されます。 最初に、クエリはその実行メモリを要求し、このメモリが付与された場合、クエリはメモリのすべてまたは一部を使用して結果またはハッシュ バケットを並べ替えます。 クエリの実行中に割り当てられたこのメモリは、メモリ許可と呼ばれます。 ご想像のとおり、クエリ実行操作が完了すると、メモリ許可は他の作業に使用するためにSQL Serverに解放されます。 そのため、メモリ許可の割り当ては本質的に一時的なものですが、長い時間が続く可能性があります。 たとえば、クエリ実行がメモリ内の非常に大きな行セットに対して並べ替え操作を実行する場合、並べ替えには数秒または数分かかる場合があり、付与されたメモリはクエリの有効期間に使用されます。
メモリ許可を持つクエリの例
実行メモリとそのクエリ プランを使用するクエリの例を次に示します。
SELECT *
FROM sys.messages
ORDER BY message_id
このクエリでは、300,000 行を超える行セットを選択して並べ替えます。 並べ替え操作によって、メモリ許可要求が誘導されます。 SSMS でこのクエリを実行すると、そのクエリ プランを表示できます。 クエリ プランの左端 SELECT の演算子を選択すると、クエリのメモリ許可情報を表示できます ( F4 キーを押して プロパティを表示します)。
また、クエリ プラン内の空白を右クリックした場合は、[ 実行プラン XML の表示 ]を選択し、同じメモリ許可情報を示す XML 要素を見つけることができます。
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
ここで説明が必要な用語がいくつかあります。 クエリでは、一定量の実行メモリ (DesiredMemory) が必要な場合があり、通常、その量 (RequestedMemory) を要求します。 実行時に、SQL Serverは、可用性 (GrantedMemory) に応じて、要求されたメモリのすべてまたは一部を許可します。 最後に、クエリでは、最初に要求されたメモリ (MaxUsedMemory) の多かれ少なかれを使用できます。 クエリ オプティマイザーが必要なメモリ量を過大評価した場合、要求されたサイズよりも小さい値が使用されます。 しかし、そのメモリは、別の要求によって使用された可能性があるため、無駄になります。 一方、オプティマイザーが必要なメモリのサイズを過小評価している場合、余分な行がディスクにスピルされて、実行時に作業が完了する可能性があります。 最初に要求されたサイズよりも多くのメモリを割り当てる代わりに、SQL Serverは余分な行をディスクにプッシュし、一時ワークスペースとして使用します。 詳細については、「 メモリ許可に関する考慮事項」の「Workfiles と Worktables」を参照してください。
用語
このメモリ コンシューマーに関して発生する可能性があるさまざまな用語を確認してみましょう。 ここでも、これらはすべて、同じメモリ割り当てに関連する概念について説明します。
クエリ実行メモリ (QE メモリ): この用語は、クエリの実行中に並べ替えまたはハッシュ メモリが使用されるという事実を強調するために使用されます。 一般的に QE メモリは、クエリの有効期間中にメモリの最大のコンシューマーです。
クエリ実行 (QE) の予約またはメモリ予約: 並べ替え操作またはハッシュ操作にメモリが必要なクエリでは、メモリの予約要求が行われます。 その予約要求は、推定カーディナリティに基づいてコンパイル時に計算されます。 その後、クエリが実行されると、SQL Serverはメモリの可用性に応じて、その要求を部分的または完全に許可します。 最後に、クエリでは、付与されたメモリの割合を使用できます。 これらのメモリ割り当てを追跡する "MEMORYCLERK_SQLQERESERVATIONS" という名前のメモリ クラーク (メモリの会計士) があります (DBCC MEMORYSTATUS または sys.dm_os_memory_clerks をチェックします)。
メモリ許可:SQL Server要求されたメモリを実行中のクエリに付与すると、メモリ許可が発生したと言われます。 "grant" という用語を使用するパフォーマンス カウンターがいくつかあります。これらのカウンターと
Memory Grants Pendingは、Memory Grants Outstanding満たされているか待機しているメモリ許可の数を表示します。 メモリ許可サイズは考慮されません。 1 つのクエリだけで、たとえば 4 GB のメモリを使用して並べ替えを実行できますが、これらのカウンターには反映されません。ワークスペース メモリ は、同じメモリを記述する別の用語です。 多くの場合、Perfmon カウンター
Granted Workspace Memory (KB)にこの用語が表示されることがあります。これは、現在、並べ替え、ハッシュ、一括コピー、インデックス作成操作に使用されているメモリの全体的な量を KB で表します。 もう 1 つのカウンターはMaximum Workspace Memory (KB)、このようなハッシュ、並べ替え、一括コピー、およびインデックス作成操作を実行する必要がある可能性がある要求で使用可能なワークスペース メモリの最大量を占めます。 ワークスペース メモリという用語は、これら 2 つのカウンターの外部で頻繁に検出されません。
QE メモリ使用率が大きい場合のパフォーマンスへの影響
ほとんどの場合、スレッドがSQL Server内のメモリを要求して何かを実行し、メモリが使用できない場合、要求はメモリ不足エラーで失敗します。 ただし、スレッドが失敗せず、メモリが使用可能になるまで待機する例外シナリオがいくつかあります。 これらのシナリオの 1 つはメモリ許可、もう 1 つはクエリ コンパイル メモリです。 SQL Serverでは、セマフォと呼ばれるスレッド同期オブジェクトを使用して、クエリ実行に付与されたメモリの量を追跡します。 SQL Server定義済みの QE ワークスペースが不足すると、メモリ不足エラーでクエリが失敗するのではなく、クエリが待機します。 ワークスペース メモリが全体的なSQL Serverメモリのかなりの割合を取ることが許可されていることを考えると、この領域のメモリを待機するとパフォーマンスに重大な影響があります。 多数の同時実行クエリで実行メモリが要求され、一緒に QE メモリ プールが使い果たされたか、いくつかの同時実行クエリがそれぞれ非常に大きな許可を要求しています。 いずれの場合も、結果として生じるパフォーマンスの問題には、次のような現象が発生する可能性があります。
- バッファー キャッシュからのデータ ページとインデックス ページは、大きなメモリ許可要求の領域を作成するためにフラッシュされている可能性があります。 つまり、クエリ要求からのページ読み取りはディスクから満たす必要があります (操作が大幅に遅くなります)。
- 他のメモリ割り当ての要求は、リソースが並べ替え、ハッシュ、またはインデックス作成操作に関連付けられているため、メモリ不足エラーで失敗する可能性があります。
- 実行メモリが必要な要求は、リソースが使用可能になるのを待っており、完了するまでに時間がかかります。 つまり、エンド ユーザーには、これらのクエリの速度が低下します。
したがって、Perfmon、動的管理ビュー (DMV)、または DBCC MEMORYSTATUSでクエリ実行メモリの待機を確認する場合は、特に問題が頻繁に発生する場合は、この問題を解決するために対処する必要があります。 詳細については、「 並べ替え操作とハッシュ操作について開発者ができること」を参照してください。
クエリ実行メモリの待機を識別する方法
QE 予約の待機を決定するには、複数の方法があります。 サーバー レベルで大きな画像を表示するために最適な機能を提供するものを選択します。 これらのツールの一部が使用できない場合があります (たとえば、Perfmon は Azure SQL Database では使用できません)。 問題を特定したら、個々のクエリ レベルでドリルダウンして、チューニングまたは書き換えが必要なクエリを確認する必要があります。
サーバー レベルでは、次の方法を使用します。
- リソース セマフォ DMV sys.dm_exec_query_resource_semaphores 詳細については、「 sys.dm_exec_query_resource_semaphores」を参照してください。
- パフォーマンス モニター カウンター 詳細については、「SQL Server Memory Manager オブジェクト」を参照してください。
- DBCC MEMORYSTATUS 詳細については、「 DBCC MEMORYSTATUS」を参照してください。
- メモリ クラーク DMV sys.dm_os_memory_clerks 詳細については、「 sys.dm_os_memory_clerks」を参照してください。
- 拡張イベント (XEvents) を使用してメモリ許可を識別する 詳細については、「 拡張イベント (XEvents)」を参照してください。
個々のクエリ レベルで、次のメソッドを使用します。
- sys.dm_exec_query_memory_grantsを使用して特定のクエリを識別する: 現在実行中のクエリ。 詳細については、「 sys.dm_exec_query_memory_grants」を参照してください。
- sys.dm_exec_requestsを使用して特定のクエリを識別する: 現在実行中のクエリ。 詳細については、「 sys.dm_exec_requests」を参照してください。
- sys.dm_exec_query_stats: クエリの履歴統計を使用して、特定のクエリを識別します。 詳細については、「 sys.dm_exec_query_stats」を参照してください。
- sys.query_store_runtime_statsでクエリ ストア (QDS) を使用して特定のクエリを識別する: QDS を使用したクエリの履歴統計。 詳細については、「 sys.query_store_runtime_stats」を参照してください。
メモリ使用量の統計情報を集計する
リソース セマフォ DMV sys.dm_exec_query_resource_semaphores
この DMV は、リソース プール (内部、既定、およびユーザー作成) と resource_semaphore (通常および小規模のクエリ要求) によってクエリ予約メモリを分割します。 便利なクエリは次のとおりです。
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
次の出力例は、約 900 MB のクエリ実行メモリが 22 の要求で使用され、さらに 3 つが待機していることを示しています。 これは、既定のプール (pool_id = 2) と通常のクエリ セマフォ (resource_semaphore_id = 0) で行われます。
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
パフォーマンス モニター カウンター
同様の情報は、現在付与されている要求 ()、待機中の許可要求 ()、メモリGranted Workspace Memory (KB)許可によって使用されるメモリの量 (Memory Grants PendingMemory Grants Outstanding) を確認できるパフォーマンス モニター カウンターを介して使用できます。 次の図では、未処理の許可は 18、保留中の許可は 2、付与されたワークスペース メモリは 828,288 KB です。 0 以外の値を持つ Perfmon カウンターは Memory Grants Pending 、メモリが不足していることを示します。
詳細については、「SQL Server Memory Manager オブジェクト」を参照してください。
- SQLServer、メモリ マネージャー: 最大ワークスペース メモリ (KB)
- SQLServer、メモリ マネージャー: メモリ許可未処理
- SQLServer、メモリ マネージャー: メモリ許可の保留中
- SQLServer、メモリ マネージャー: ワークスペース メモリの付与 (KB)
DBCC MEMORYSTATUS
クエリ予約メモリの詳細を確認できるもう 1 つの場所は DBCC MEMORYSTATUS 、(クエリ メモリ オブジェクト セクション) です。 ユーザー クエリの出力を Query Memory Objects (default) 確認できます。 PoolAdmin という名前のリソース プールを使用してResource Governorを有効にしている場合は、たとえば、 と Query Memory Objects (PoolAdmin)の両方Query Memory Objects (default)を確認できます。
18 個の要求にクエリ実行メモリが付与され、2 つの要求がメモリを待機しているシステムからの出力例を次に示します。 使用可能なカウンターは 0 であり、使用可能なワークスペース メモリがなくなったことを示します。 この事実は、2 つの待機中の要求について説明します。 は Wait Time 、要求が待機キューに格納されてからの経過時間をミリ秒単位で示します。 これらのカウンターの詳細については、「 クエリ メモリ オブジェクト」を参照してください。
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS また、クエリ実行メモリを追跡するメモリ クラークに関する情報も表示されます。 次の出力は、クエリ実行 (QE) の予約に割り当てられたページが 800 MB を超えていることを示しています。
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
メモリ クラーク DMV sys.dm_os_memory_clerks
セクションベース DBCC MEMORYSTATUSとは異なる表形式の結果セットが必要な場合は、同様の情報 にsys.dm_os_memory_clerks を使用できます。 記憶係を MEMORYCLERK_SQLQERESERVATIONS 探します。 ただし、クエリ メモリ オブジェクトは、この DMV では使用できません。
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
出力例を次に示します。
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
拡張イベント (XEvents) を使用してメモリ許可を識別する
メモリ許可情報を提供し、トレースを介してこの情報をキャプチャできるようにする拡張イベントが複数あります。
- sqlserver.additional_memory_grant: クエリが実行中により多くのメモリ許可を取得しようとしたときに発生します。 この追加のメモリ許可を取得できないと、クエリの速度が低下する可能性があります。
- sqlserver.query_memory_grant_blocking: メモリ許可の待機中にクエリが他のクエリをブロックしているときに発生します。
- sqlserver.query_memory_grant_info_sampling: メモリ許可情報を提供するランダムにサンプリングされたクエリの最後に発生します (テレメトリなど、 を使用できます)。
- sqlserver.query_memory_grant_resource_semaphores: リソース ガバナー リソース プールごとに 5 分間隔で発生します。
- sqlserver.query_memory_grant_usage: メモリ許可が 5 MB を超えるクエリのクエリ処理の終了時に発生し、メモリ許可の不正確さについてユーザーに知らせます。
- sqlserver.query_memory_grants: メモリ許可を持つクエリごとに 5 分間隔で発生します。
メモリ許可フィードバック拡張イベント
クエリ処理メモリ許可フィードバック機能については、「 メモリ許可フィードバック」を参照してください。
- sqlserver.memory_grant_feedback_loop_disabled: メモリ許可フィードバック ループが無効になっている場合に発生します。
- sqlserver.memory_grant_updated_by_feedback: メモリ許可がフィードバックによって更新されたときに発生します。
メモリ許可に関連するクエリ実行警告
- sqlserver.execution_warning: T-SQL ステートメントまたはストアド プロシージャがメモリの許可を 1 秒以上待機したとき、またはメモリの取得の最初の試行が失敗したときに発生します。 このイベントを、パフォーマンスに影響を与える競合の問題のトラブルシューティングを行う待機を識別するイベントと組み合わせて使用します。
- sqlserver.hash_spill_details: ハッシュ結合のビルド入力を処理するためのメモリが不足している場合、ハッシュ処理の終了時に発生します。 このイベントを または イベントと共に使用して、
query_pre_execution_showplanquery_post_execution_showplan生成されたプランでハッシュ スピルの原因となっている操作を特定します。 - sqlserver.hash_warning: ハッシュ結合のビルド入力を処理するためのメモリが不足している場合に発生します。 これにより、ビルド入力のパーティション分割時にハッシュ再帰が発生し、ビルド入力のパーティション分割が最大再帰レベルを超えた場合はハッシュのベイルアウトが発生します。 このイベントを、 または
query_post_execution_showplanのいずれかのquery_pre_execution_showplanイベントと共に使用して、生成されたプランでハッシュ警告の原因となっている操作を特定します。 - sqlserver.sort_warning: 実行中のクエリの並べ替え操作がメモリに収まらない場合に発生します。 このイベントは、インデックスの作成によって発生する並べ替え操作に対しては生成されません。クエリ内の並べ替え操作に対してのみ生成されます。 (たとえば、
Order Byステートメント内の 。Selectこのイベントを使用して、並べ替え操作のために実行速度が遅いクエリを特定します。特に、 = 2 の場合warning_typeは、並べ替えに複数のデータのパスが必要であることを示します。
メモリ許可情報を含むイベントの生成を計画する
拡張イベントを生成する次のクエリ プランには、既定 でgranted_memory_kb フィールドと ideal_memory_kb フィールドが含まれています。
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
列ストア インデックスの作成
XEvents でカバーされる領域の 1 つは、列ストアのビルド中に使用される実行メモリです。 使用可能なイベントの一覧を次に示します。
- sqlserver.column_store_index_build_low_memory: ストレージ エンジンでメモリ不足状態が検出され、行グループのサイズが縮小されました。 関心のある列がいくつかあります。
- sqlserver.column_store_index_build_memory_trace: インデックスのビルド中にメモリ使用量をトレースします。
- sqlserver.column_store_index_build_memory_usage_scale_down: ストレージ エンジンがスケールダウンされました。
- sqlserver.column_store_index_memory_estimation: COLUMNSTORE 行グループビルド中のメモリ推定結果を表示します。
特定のクエリを識別する
個々の要求レベルを見ると、2 種類のクエリが見つかる場合があります。 大量のクエリ実行メモリを消費しているクエリと、同じメモリを待機しているクエリ。 後者のグループは、メモリ許可の控えめなニーズを持つ要求で構成される場合があり、その場合は、他の場所で注意を集中できます。 しかし、巨大なメモリ サイズを要求している場合は、原因になる可能性もあります。 該当する場合は、それらに焦点を当てます。 1 つの特定のクエリが違反者であることが一般的ですが、その多くのインスタンスが生成されます。 メモリ許可を取得するインスタンスによって、同じクエリの他のインスタンスが許可を待機しています。 特定の状況に関係なく、最終的には、クエリと要求された実行メモリのサイズを特定する必要があります。
sys.dm_exec_query_memory_grantsを使用して特定のクエリを識別する
個々の要求と、要求済みで許可されているメモリ サイズを表示するには、動的管理ビューに対してクエリを sys.dm_exec_query_memory_grants 実行します。 この DMV には、履歴情報ではなく、現在実行されているクエリに関する情報が表示されます。
次のステートメントは、DMV からデータを取得し、結果としてクエリ テキストとクエリ プランもフェッチします。
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
アクティブな QE メモリ消費中のクエリの省略されたサンプル出力を次に示します。 ほとんどのクエリには、NULL 以外の数値でgranted_memory_kbused_memory_kb示されているように、メモリが付与されています。 要求が許可されなかったクエリは、実行メモリと を granted_memory_kb = NULL待機しています。 また、 = 6 の待機キューに queue_id 配置されます。 これは wait_time_ms 、待機中の約 37 秒を示します。 セッション 72 は、 = 1 で示されているように wait_order 許可を取得するために次の行に並び、セッション 74 は = 2 の後に wait_order 来ます。
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
sys.dm_exec_requestsを使用して特定のクエリを識別する
SQL Serverには、クエリがメモリ許可 RESOURCE_SEMAPHOREを待機していることを示す待機の種類があります。 この待機の種類は、個々の要求に sys.dm_exec_requests 対して確認できます。 この後者の DMV は、どのクエリが不十分な許可メモリの影響を受けるかを特定するための最良の出発点です。 また、SQL Server レベルで集計されたデータ ポイントとしてsys.dm_os_wait_statsで待機を確認RESOURCE_SEMAPHOREすることもできます。 この待機の種類は、他の同時実行クエリでメモリが使い切れたためにクエリ メモリ要求を許可できない場合に表示されます。 待機要求の数が多く、待機時間が長い場合は、実行メモリまたは大きなメモリ要求サイズを使用する同時実行クエリの数が多すぎる場合を示します。
注:
メモリ許可の待機時間は有限です。 過剰な待機 (たとえば、20 分以上) の後、SQL Serverクエリがタイムアウトし、エラー 8645 が発生します。"メモリ リソースがクエリを実行するのを待っている間にタイムアウトが発生しました。 クエリを再実行します。を参照timeout_secsys.dm_exec_query_memory_grantsすると、サーバー レベルで設定されたタイムアウト値が表示される場合があります。 タイムアウト値は、SQL Serverバージョンによって若干異なる場合があります。
を使用 sys.dm_exec_requestsすると、メモリが付与されているクエリとその許可のサイズを確認できます。 また、待機の種類を探すことで、メモリ許可を現在待機しているクエリを RESOURCE_SEMAPHORE 特定できます。 許可された要求と待機中の要求の両方を示すクエリを次に示します。
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
サンプル出力は、2 つの要求にメモリが付与され、他の 2 ダースが許可を待機していることを示しています。 列は granted_query_memory 、サイズを 8 KB ページで報告します。 たとえば、値が 34,709 の場合、34,709 * 8 KB = 277,672 KB のメモリが付与されます。
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
sys.dm_exec_query_statsを使用して特定のクエリを識別する
この時点でメモリ許可の問題が発生していないが、問題のあるクエリを特定する場合は、 を使用してクエリの履歴データを sys.dm_exec_query_stats確認できます。 データの有効期間は、各クエリのクエリ プランに関連付けられます。 プランがプラン キャッシュから削除されると、対応する行がこのビューから削除されます。 つまり、DMV は、SQL Serverの再起動後、またはメモリ不足によってプラン キャッシュが解放された後も保持されない統計をメモリに保持します。 つまり、ここでは、特に集計クエリ統計に関する貴重な情報を見つけることができます。 誰かが最近、クエリからの大規模なメモリ許可の表示を報告したかもしれませんが、サーバーのワークロードを見ると、問題がなくなったことを発見する可能性があります。 この状況では、 sys.dm_exec_query_stats 他の DVM では得られない分析情報を提供できます。 最大量の実行メモリを消費した上位 20 個のステートメントを見つけるのに役立つサンプル クエリを次に示します。 この出力では、クエリ構造が同じ場合でも個々のステートメントが表示されます。 たとえば、 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 とは別の行 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 です (フィルター述語の値のみが異なります)。 クエリは、最大許可サイズが 5 MB を超える上位 20 個のステートメントを取得します。
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
によって集計されたクエリを見ることで、さらに強力な分析情報を query_hash得ることができます。 この例では、クエリ プランが最初にキャッシュされてから、すべてのインスタンスにわたってクエリ ステートメントの平均、最大、最小の許可サイズを検索する方法を示します。
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
列には Sample_Statement_Text 、クエリ ハッシュに一致するクエリ構造の例が示されていますが、ステートメント内の特定の値に関係なく読み取る必要があります。 たとえば、ステートメントに が含まれているWHERE Id = 5場合は、より一般的な形式で読み取ります。 WHERE Id = @any_value
次に示すのは、選択した列のみを含むクエリの省略されたサンプル出力です。
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
sys.query_store_runtime_statsでクエリ ストア (QDS) を使用して特定のクエリを識別する
クエリ ストア有効にしている場合は、永続化された履歴統計を利用できます。 からのデータsys.dm_exec_query_statsとは対照的に、これらの統計は、データベースに格納されているため、再起動またはメモリ不足SQL Server生き残ります。 QDS には、サイズ制限とアイテム保持ポリシーもあります。 詳細については、「最適なクエリ ストアキャプチャ モードを設定する」および「クエリ ストアを管理するためのベスト プラクティス」の「クエリ ストアの最も関連性の高いデータを保持する」セクションを参照してください。
このクエリを使用して、データベースクエリ ストア有効になっているかどうかを確認します。
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1調査する特定のデータベースのコンテキストで、次の診断クエリを実行します。
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)ここでの原則は と同じです
sys.dm_exec_query_stats。ステートメントの集計統計が表示されます。 ただし、1 つの違いは、QDS では、SQL Server全体ではなく、このデータベースのスコープ内のクエリのみを調べることです。 そのため、特定のメモリ許可要求が実行されたデータベースを知る必要がある場合があります。 それ以外の場合は、大きなメモリ許可が見つかるまで、複数のデータベースでこの診断クエリを実行します。省略されたサンプル出力を次に示します。
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
カスタム診断クエリ
前に示した 3 つを含む、複数のビューのデータを結合するクエリを次に示します。 によって提供されるサーバー レベルの統計情報に加えて、 および sys.dm_exec_query_memory_grantsを介したsys.dm_exec_requestsセッションとその許可のより詳細なビューが提供されますsys.dm_exec_query_resource_semaphores。
注:
このクエリでは、 (通常の sys.dm_exec_query_resource_semaphores リソース セマフォの場合は 1 行、小クエリ リソース セマフォの場合は 1 行) が使用されるため、セッションごとに 2 行が返されます。
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
注:
ヒントは LOOP JOIN 、クエリ自体によるメモリの付与を回避するために、この診断クエリで使用され、句は使用されません ORDER BY 。 診断クエリが許可自体を待機する場合、メモリ許可を診断する目的は負けます。 ヒントによって LOOP JOIN 診断クエリの速度が低下する可能性がありますが、この場合は診断結果を取得することがより重要です。
選択された列のみを含む、この診断クエリからの省略されたサンプル出力を次に示します。
| session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
サンプル出力は、= 60 によって送信された session_id クエリが、要求された 9 MB のメモリ許可を正常に取得したが、クエリ実行を正常に開始するために必要だったのは 7 MB に過ぎない方法を明確に示しています。 最後に、クエリでは、サーバーから受信した 9 MB のうち 1 MB のみを使用しました。 出力は、セッション 75 と 86 がメモリ許可を待機していることも示しています。したがって、 RESOURCE_SEMAPHOREwait_typeは です。 待機時間は 1,300 秒 (21 分) を超え、その granted_memory_mb 待機時間は です NULL。
この診断クエリはサンプルであるため、ニーズに合わせて自由に変更できます。 このクエリのバージョンは、Microsoft SQL Server サポートが使用する診断ツールでも使用されます。
診断ツール
Microsoft SQL Server テクニカル サポートがログを収集し、問題をより効率的にトラブルシューティングするために使用する診断ツールがあります。 SQL LogScout と Pssdiag Configuration Manager (SQLDiag と共に) は、メモリ許可の問題を診断するのに役立つ、前述の DMV とパフォーマンス モニター カウンターの出力を収集します。
LightPerf、GeneralPerf、または DetailedPerf シナリオで SQL LogScout を実行すると、ツールは必要なログを収集します。 その後、YourServer_PerfStats.out を手動で調べて、検索して-- dm_exec_query_memory_grants --出力を探-- dm_exec_query_resource_semaphores --すことができます。 または、手動で調べるのではなく、SQL Nexus を使用して、SQL LogScout または PSSDIAG からの出力をSQL Server データベースにインポートできます。 SQL Nexus では、 tbl_dm_exec_query_resource_semaphores メモリ許可の診断に必要な情報を含む 2 つのテーブル と tbl_dm_exec_query_memory_grantsが作成されます。 SQL LogScout と PSSDIAG も、 の形式で Perfmon ログを収集します。「パフォーマンス モニター カウンター」セクションで説明されているパフォーマンス カウンターを確認するために使用できる BLG ファイル。
開発者または DBA にメモリ許可が重要なのはなぜですか
Microsoft のサポート エクスペリエンスに基づいて、メモリ付与の問題は、メモリ関連の最も一般的な問題の一部である傾向があります。 多くの場合、アプリケーションは一見単純なクエリを実行します。その結果、大量の並べ替え操作やハッシュ操作が原因で、SQL Serverでパフォーマンスの問題が発生する可能性があります。 このようなクエリは、多くのSQL Serverメモリを消費するだけでなく、他のクエリがメモリが使用可能になるまで待機するため、パフォーマンスのボトルネックになります。
ここで説明するツール (DMV、Perfmon カウンター、および実際のクエリ プラン) を使用して、どのクエリが大規模な許可コンシューマーであるかを特定できます。 その後、これらのクエリを調整または書き換えて、ワークスペースのメモリ使用量を解決または削減できます。
並べ替え操作とハッシュ操作について開発者ができること
大量のクエリ予約メモリを消費する特定のクエリを特定したら、これらのクエリを再設計することでメモリ許可を減らす手順を実行できます。
クエリの並べ替え操作とハッシュ操作の原因
最初の手順は、クエリ内の操作がメモリ許可につながる可能性があることを認識することです。
クエリで SORT 演算子が使用される理由:
ORDER BY (T-SQL) では、最終的な結果としてストリーミングされる前に行が並べ替えられます。
GROUP BY (T-SQL) は、グループ化された列を並べ替える基になるインデックスが存在しない場合、グループ化する前にクエリ プランに並べ替え演算子を導入する可能性があります。
DISTINCT (T-SQL) は と同様に
GROUP BY動作します。 個別の行を識別するために、中間の結果が並べ替えられた後、重複が削除されます。 順序付けされたインデックスシークまたはスキャンのためにデータがまだ並べ替えられていない場合、オプティマイザーはこの演算子の前に演算子を使用Sortします。マージ結合演算子は、クエリ オプティマイザーによって選択された場合、結合された両方の入力が並べ替えられている必要があります。 SQL Serverは、いずれかのテーブルの結合列でクラスター化インデックスを使用できない場合に並べ替えをトリガーする可能性があります。
クエリで HASH クエリ プラン演算子が使用される理由:
この一覧は網羅的ではありませんが、ハッシュ操作の最も一般的な理由が含まれています。 クエリ プランを分析 して、ハッシュ一致操作を識別します。
JOIN (T-SQL): テーブルを結合する場合、SQL Serverには、3 つの物理演算子 、、
Nested LoopMerge JoinおよびHash Joinのいずれかを選択できます。 SQL Serverハッシュ結合を選択する場合は、中間結果を格納して処理するために QE メモリが必要です。 通常、適切なインデックスが不足すると、Hash Joinこの最もリソースコストの高い結合演算子 である 可能性があります。 識別Hash Matchするクエリ プランを調べるには、「論理演算子と物理演算子リファレンス」を参照してください。DISTINCT (T-SQL): 演算子を
Hash Aggregate使用して、行セット内の重複を排除できます。 クエリ プランで (Aggregate) を検索Hash Matchするには、「論理演算子と物理演算子リファレンス」を参照してください。UNION (T-SQL): これは に
DISTINCT似ています。 をHash Aggregate使用して、この演算子の重複を削除できます。SUM/AVG/MAX/MIN (T-SQL): 集計操作は、 として
Hash Aggregate実行される可能性があります。 クエリ プランで (Aggregate) を検索Hash Matchするには、「論理演算子と物理演算子リファレンス」を参照してください。
これらの一般的な理由を知ることは、可能な限り、SQL Serverに来る大きなメモリ許可要求を排除するのに役立ちます。
並べ替え操作とハッシュ操作または許可サイズを減らす方法
- 統計を最新の状態に保ちます。 この基本的な手順により、多くのレベルのクエリのパフォーマンスが向上し、クエリ プランを選択するときにクエリ オプティマイザーに最も正確な情報が確実に含まれるようにします。 SQL Serverは、統計に基づいてメモリ許可を要求するサイズを決定します。 古い統計は、許可要求の過大評価または過小評価を引き起こす可能性があるため、不必要に高い許可要求が発生したり、結果がディスクにスピルされたりする可能性があります。 データベースで 統計の自動更新 が有効になっていることを確認するか、 UPDATE STATISTICS または sp_updatestatsを使用して静的を更新します。
- テーブルからの行数を減らします。 より制限の厳しい WHERE フィルターまたは JOIN を使用し、行数を減らすと、クエリ プランの後続の並べ替えにより、より小さい結果セットの順序付けまたは集計が行われます。 中間結果セットが小さいほど、ワーキング セットのメモリが少なくて済みます。 これは、開発者が作業セットのメモリを節約するだけでなく、CPU と I/O を削減するために従うことができる一般的なルールです (この手順は常に可能ではありません)。 適切に記述されたリソース効率の高いクエリが既に設定されている場合は、このガイドラインが満たされています。
- 結合列にインデックスを作成して、マージ結合を支援します。 クエリ プランの中間操作は、基になるテーブルのインデックスの影響を受けます。 たとえば、テーブルに結合列のインデックスがなく、マージ結合が最もコスト効率の高い結合演算子であることが判明した場合、結合を実行する前にそのテーブルのすべての行を並べ替える必要があります。 代わりに、列にインデックスが存在する場合は、並べ替え操作を削除できます。
- ハッシュ操作を回避するためにインデックスを作成します。 一般的に、基本的なクエリチューニングは、クエリに適切なインデックスがあるかどうかを確認することから始まり、読み取りを減らし、可能な限り大規模な並べ替えやハッシュ操作を最小限に抑えたり排除したりするのに役立ちます。 ハッシュ結合は、通常、大きい、並べ替えられていない、インデックスのない入力を処理するために選択されます。 インデックスを作成すると、このオプティマイザー戦略が変更され、データ取得が高速化される可能性があります。 インデックスの作成については、「データベース エンジン チューニング アドバイザー」および「インデックス候補が見つからない非クラスター化インデックスをチューニングする」を参照してください。
- を使用する集計クエリに適した場合は、COLUMNSTORE インデックスを使用
GROUP BYします。 非常に大きな行セットを処理し、通常は "グループ化" 集計を実行する分析クエリでは、作業を完了するために大きなメモリ チャンクが必要になる場合があります。 順序付けされた結果を提供するインデックスが使用できない場合、並べ替えはクエリ プランに自動的に導入されます。 非常に大きな結果の一種は、高価なメモリ許可につながる可能性があります。 - 不要な場合は を
ORDER BY削除します。 結果を独自の方法で並べ替えたり、ユーザーが表示されるデータの順序を変更したりするアプリケーションに結果がストリーミングされる場合は、SQL Server側で並べ替えを実行する必要はありません。 サーバーが生成する順序でデータをアプリケーションにストリーミングし、エンド ユーザーが独自に並べ替えるだけです。 Power BI や Reporting Services などのレポート アプリケーションは、エンド ユーザーがデータを並べ替えることを許可するそのようなアプリケーションの例です。 - T-SQL クエリに結合が存在する場合は、 慎重に LOOP JOIN ヒントを使用することを検討してください。 この手法では、メモリ許可を使用するハッシュ結合またはマージ結合を回避できます。 ただし、結合を強制するとクエリが大幅に遅くなる可能性があるため、このオプションは最後の手段としてのみ推奨されます。 ワークロードをストレス テストして、これがオプションであることを確認します。 場合によっては、入れ子になったループ結合がオプションでなくてもかまいません。 この場合、SQL Serverエラー MSSQLSERVER_8622で失敗する可能性があります。"クエリ プロセッサでは、このクエリで定義されているヒントが原因でクエリ プランを生成できませんでした。
メモリ許可クエリ ヒント
2012 SP3 SQL Server以降、クエリ ヒントが存在しており、クエリごとにメモリ許可のサイズを制御できます。 このヒントを使用する方法の例を次に示します。
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
ここでは保守的な値を使用することをお勧めします。特に、クエリの多くのインスタンスが同時に実行されると予想される場合に使用することをお勧めします。 運用環境に合わせてワークロードをストレス テストし、使用する値を決定します。
詳細については、「 MAX_GRANT_PERCENTとMIN_GRANT_PERCENT」を参照してください。
Resource Governor
QE メモリは、MIN_MEMORY_PERCENTとMAX_MEMORY_PERCENTの設定を使用するときに実際に制限Resource Governorメモリです。 大規模なメモリ許可を引き起こすクエリを特定したら、セッションまたはアプリケーションで使用されるメモリを制限できます。 ワークロード グループでは、SQL Server インスタンスに付与できる最大 25% のメモリをクエリで使用できることに注目defaultしてください。 詳細については、「リソース プールのResource Governor」と「CREATE WORKLOAD GROUP」を参照してください。
アダプティブ クエリ処理とメモリ許可フィードバック
SQL Server 2017 では、メモリ許可フィードバック機能が導入されました。 これにより、クエリ実行エンジンは、以前の履歴に基づいてクエリに与えられた許可を調整できます。 目標は、可能な限り許可のサイズを小さくするか、より多くのメモリが必要なときにそれを増やすことです。 この機能は、次の 3 つのウェーブでリリースされています。
- SQL Server 2017 のバッチ モード メモリ許可フィードバック
- SQL Server 2019 の行モード メモリ許可フィードバック
- SQL Server 2022 のクエリ ストアとパーセンタイル付与を使用したメモリ許可のディスク上の永続化に関するフィードバック
詳細については、「 メモリ許可フィードバック」を参照してください。 メモリ許可機能により、実行時にクエリのメモリ許可のサイズが小さくなるため、大きな許可要求に起因する問題が軽減される可能性があります。 この機能は、特に 2019 年SQL Server以降のバージョンで行モードのアダプティブ処理が利用できる場合に、クエリの実行に起因するメモリの問題に気付かない場合もあります。 ただし、この機能が (既定ではオン) に設定されていて、QE メモリの消費量が大きい場合は、前に説明した手順を適用してクエリを書き換えます。
SQL Serverまたは OS メモリを増やす
クエリの不要なメモリ許可を減らす手順を実行した後でも、関連するメモリ不足の問題が引き続き発生する場合は、ワークロードにより多くのメモリが必要になります。 そのため、システムに十分な物理メモリがある場合は、設定をmax server memory使用SQL Serverメモリを増やすことを検討してください。 OS やその他のニーズに合わせてメモリの約 25% を残すことに関する推奨事項に従ってください。 詳細については、「 サーバー メモリ構成オプション」を参照してください。 システムで十分なメモリが使用できない場合は、物理 RAM の追加を検討するか、仮想マシンの場合は、VM の専用 RAM を増やします。
メモリ許可の内部
クエリ実行メモリに関するいくつかの内部の詳細については、 SQL Server メモリ許可 の理解に関するブログ記事を参照してください。
メモリ許可の使用量が多いパフォーマンス シナリオを作成する方法
最後に、次の例では、クエリ実行メモリの大量消費をシミュレートし、 を RESOURCE_SEMAPHORE待機しているクエリを導入する方法を示します。 これを行うと、この記事で説明されている診断ツールと手法を使用する方法を学習できます。
警告
運用システムではこれを使用しないでください。 このシミュレーションは、概念を理解するのに役立ち、より良い学習に役立ちます。
テスト サーバーで、RML ユーティリティとSQL Serverをインストールします。
SQL Server Management Studioなどのクライアント アプリケーションを使用して、SQL Serverの最大サーバー メモリ設定を 1,500 MB に下げる:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREコマンド プロンプトを開き、ディレクトリを RML ユーティリティ フォルダーに変更します。
cd C:\Program Files\Microsoft Corporation\RMLUtilsostress.exe を使用して、テスト SQL Serverに対して複数の同時要求を生成します。 この例では、30 個の同時セッションを使用しますが、その値を変更できます。
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30前に説明した診断ツールを使用して、メモリ許可の問題を特定します。
大きなメモリ許可に対処する方法の概要
- クエリを書き換える。
- 統計を更新し、定期的に更新します。
- 特定されたクエリまたはクエリに適切なインデックスを作成します。 インデックスを使用すると、処理される行の数が多くなり、アルゴリズムが
JOIN変更され、許可のサイズが縮小されたり、完全に削除されたりすることがあります。 OPTION(min_grant_percent = XX、max_grant_percent = XX) ヒントを使用します。- Resource Governorを使用します。
- SQL Server 2017 および 2019 では、アダプティブ クエリ処理を使用するため、メモリ許可フィードバック メカニズムを使用して、実行時にメモリ許可のサイズを動的に調整できます。 この機能により、最初にメモリ許可の問題が発生しなくなる可能性があります。
- SQL Serverまたは OS メモリを増やします。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示