SQL Server でのメモリ許可が原因で発生するパフォーマンスの低下またはメモリ不足の問題のトラブルシューティング
メモリ許可とは
クエリ実行 (QE) 予約、クエリ実行メモリ、ワークスペース メモリ、メモリ予約とも呼ばれるメモリ許可は、クエリ実行時のメモリ使用量を表します。 SQL Server は、クエリの実行中に、次の 1 つ以上の目的でこのメモリを割り当てます。
- 並べ替え操作
- ハッシュ操作
- 一括コピー操作 (一般的な問題ではありません)
- インデックスの作成 (ハッシュ ディクショナリ/テーブルが実行時にインデックス作成に使用されるため、COLUMNSTORE インデックスへの挿入を含む) (一般的な問題ではありません)
コンテキストを提供するために、その有効期間中に、クエリは、実行する必要がある内容に応じて、異なるメモリ アロケーターまたはクラークからメモリを要求する場合があります。 たとえば、クエリが最初に解析されてコンパイルされると、コンパイル メモリが消費されます。 クエリがコンパイルされると、そのメモリが解放され、結果のクエリ プランがプラン キャッシュ メモリに格納されます。 プランがキャッシュされると、クエリを実行する準備が整います。 クエリが並べ替え操作、ハッシュ一致操作 (JOIN または集計)、または COLUMNSTORE インデックスへの挿入を実行すると、クエリ実行アロケーターのメモリが使用されます。 最初に、クエリはその実行メモリを要求し、後でこのメモリが許可されると、クエリはメモリのすべてまたは一部を並べ替え結果またはハッシュ バケットに使用します。 クエリの実行中に割り当てられるこのメモリは、メモリ許可と呼ばれます。 ご想像のとおり、クエリ実行操作が完了すると、他の作業に使用するためにメモリ許可が SQL Server に解放されます。 そのため、メモリ許可の割り当ては本質的に一時的なものですが、長い時間続く可能性があります。 たとえば、クエリ実行がメモリ内の非常に大きな行セットに対して並べ替え操作を実行する場合、並べ替えには数秒または数分かかる場合があり、付与されたメモリはクエリの有効期間中に使用されます。
メモリ許可を持つクエリの例
実行メモリとそのクエリ プランを使用するクエリの例を次に示します。
SELECT *
FROM sys.messages
ORDER BY message_id
このクエリでは、300,000 行を超える行セットが選択され、並べ替えられます。 並べ替え操作により、メモリ許可要求が誘発されます。 SSMS でこのクエリを実行すると、そのクエリ プランを表示できます。 クエリ プランの左端の SELECT 演算子を選択すると、クエリのメモリ許可情報を表示できます ( F4 キーを押すと、 Properties が表示されます)。
また、クエリ プランの空白を右クリックすると、 Show 実行プラン 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 は、最初に要求されたサイズよりも多くのメモリを割り当てる代わりに、余分な行をディスクにプッシュし、一時ワークスペースとして使用します。 詳細については、「 Memory Grant Considerations の Workfiles と Worktables」を参照してください。
用語
このメモリ コンシューマーに関して発生する可能性があるさまざまな用語を確認してみましょう。 ここでも、これらのすべてが同じメモリ割り当てに関連する概念を記述します。
クエリ実行メモリ (QE メモリ): この用語は、クエリの実行中に並べ替えまたはハッシュ メモリが使用されるという事実を強調するために使用されます。 一般に、QE メモリは、クエリの有効期間中に最大のメモリ コンシューマーです。
クエリ実行 (QE) 予約またはメモリ予約: クエリで並べ替え操作またはハッシュ操作にメモリが必要な場合は、メモリの予約要求を行います。 その予約要求は、推定カーディナリティに基づいてコンパイル時に計算されます。 後でクエリが実行されると、SQL Server はメモリの可用性に応じて、その要求を部分的または完全に許可します。 最後に、クエリでは、許可されたメモリの割合が使用される場合があります。 これらのメモリ割り当てを追跡する "MEMORYCLERK_SQLQERESERVATIONS" という名前のメモリ クラーク (メモリの会計士) があります ( DBCC MEMORYSTATUS または sys.dm_os_memory_clerksを確認してください)。
メモリ許可: SQL Server が要求されたメモリを実行中のクエリに付与すると、メモリ許可が発生したと言われます。 "grant" という用語を使用するパフォーマンス カウンターがいくつかあります。これらのカウンター (
Memory Grants OutstandingとMemory Grants Pending) には、満たされているメモリ許可または待機中のメモリ許可の数が表示されます。 メモリ許可のサイズは考慮されません。 1 つのクエリだけで、並べ替えを実行するために 4 GB のメモリが消費される可能性がありますが、どちらのカウンターにも反映されません。ワークスペース メモリ は、同じメモリを記述するもう 1 つの用語です。 多くの場合、Perfmon カウンター
Granted Workspace Memory (KB)にこの用語が表示されることがあります。これは、現在、並べ替え、ハッシュ、一括コピー、インデックス作成操作に使用されているメモリの全体的な量を KB 単位で表します。 別のカウンターであるMaximum Workspace Memory (KB)は、ハッシュ、並べ替え、一括コピー、インデックス作成の操作を行う必要がある可能性がある要求で使用可能なワークスペース メモリの最大量を考慮します。 ワークスペース メモリという用語は、これら 2 つのカウンターの外部で頻繁に検出されません。
QE メモリ使用率が大きい場合のパフォーマンスへの影響
ほとんどの場合、スレッドが SQL Server 内のメモリを要求して何かを取得し、メモリを使用できない場合、要求はメモリ不足エラーで失敗します。 ただし、スレッドが失敗せず、メモリが使用可能になるまで待機する例外シナリオがいくつかあります。 これらのシナリオの 1 つはメモリ許可で、もう 1 つはクエリ コンパイル メモリです。 SQL Server では、 semaphore と呼ばれるスレッド同期オブジェクトを使用して、クエリの実行に許可されているメモリの量を追跡します。 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) を使用してメモリ許可を識別する 詳細については、「 Extended Events (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」を参照してください。
- クエリ ストア (QDS) と sys.query_store_runtime_stats: 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)
パフォーマンス モニター カウンター
同様の情報は、パフォーマンス モニター カウンターを介して入手できます。ここで、現在付与されている要求 (Memory Grants Outstanding)、待機中の許可要求 (Memory Grants Pending)、メモリ許可によって使用されるメモリの量 (Granted Workspace Memory (KB)) を確認できます。 次の図では、未処理の許可は 18、保留中の許可は 2、付与されたワークスペース メモリは 828,288 KB です。 0 以外の値を持つ Memory Grants Pending Perfmon カウンターは、メモリが使い果たされたことを示します。
詳細については、「 SQL Server Memory Manager オブジェクト」を参照してください。
- SQLServer、メモリ マネージャー: 最大ワークスペース メモリ (KB)
- SQLServer、メモリ マネージャー: メモリ許可未処理
- SQLServer、メモリ マネージャー: メモリ許可の保留中
- SQLServer、メモリ マネージャー: 付与されたワークスペース メモリ (KB)
DBCC MEMORYSTATUS
クエリ予約メモリの詳細を確認できるもう 1 つの場所は、 DBCC MEMORYSTATUS です (Query Memory Objects セクション)。 ユーザー クエリの Query Memory Objects (default) 出力を見ることができます。 たとえば、 PoolAdmin という名前のリソース プールで Resource Governor を有効にしている場合は、 Query Memory Objects (default) と Query Memory Objects (PoolAdmin)の両方を確認できます。
18 個の要求にクエリ実行メモリが付与され、2 つの要求がメモリを待機しているシステムからの出力例を次に示します。 使用可能なカウンターは 0 で、使用可能なワークスペース メモリがなくなったことを示します。 この事実は、2 つの待機中の要求について説明します。 Wait Timeは、要求が待機キューに格納されてからの経過時間をミリ秒単位で示します。 これらのカウンターの詳細については、「 Query メモリ オブジェクトを参照してください。
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_showplanイベントまたはquery_post_execution_showplanイベントと共に使用して、生成されたプランでハッシュ スピルの原因となっている操作を特定します。 - sqlserver.hash_warning: ハッシュ結合のビルド入力を処理するのに十分なメモリがない場合に発生します。 これにより、ビルド入力のパーティション分割時にハッシュ再帰が発生し、ビルド入力のパーティション分割が最大再帰レベルを超えた場合はハッシュのベイルアウトが発生します。 このイベントを
query_pre_execution_showplanイベントまたはquery_post_execution_showplanイベントと共に使用して、生成されたプランでハッシュ警告の原因となっている操作を特定します。 - sqlserver.sort_warning: 実行中のクエリの並べ替え操作がメモリに収まらない場合に発生します。 このイベントは、インデックスの作成によって発生する並べ替え操作に対しては生成されず、クエリでの並べ替え操作に対してのみ生成されます。 (たとえば、
Selectステートメント内のOrder Byなど)。このイベントを使用して、並べ替え操作のために実行速度が遅いクエリを特定します。特に、warning_type= 2 の場合は、データに対する複数のパスが並べ替えに必要であることを示します。
メモリ許可情報を含むイベントの生成を計画する
拡張イベントを生成する次のクエリ プランには、既定で 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 メモリ消費中のクエリの省略されたサンプル出力を次に示します。 ほとんどのクエリには、 granted_memory_kb で示されているようにメモリが付与され、null 以外の数値 used_memory_kb 。 要求が許可されなかったクエリは、実行メモリと granted_memory_kb = NULLを待機しています。 また、 queue_id = 6 の待機キューに配置されます。 それらの wait_time_ms は、約 37 秒の待機を示します。 セッション 72 は、 wait_order = 1 で示されているように許可を取得するために次に並び、セッション 74 は wait_order = 2 の後に来ます。
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を使用して特定のクエリを識別する
クエリがメモリ許可RESOURCE_SEMAPHOREを待機していることを示す SQL Server には、待機型があります。 この待機の種類は、個々の要求の sys.dm_exec_requests で確認できます。 この後者の DMV は、どのクエリが十分な許可メモリの対象であるかを特定するのに最適な開始点です。 また、SQL Server レベルで集計されたデータ ポイントとしてsys.dm_os_wait_statsで待機RESOURCE_SEMAPHOREを確認することもできます。 この待機の種類は、他の同時実行クエリがメモリを使い切ったためにクエリ メモリ要求を許可できない場合に表示されます。 待機中の要求の数が多く、待機時間が長い場合は、実行メモリまたは大きなメモリ要求サイズを使用する同時実行クエリの数が過剰であることを示します。
Note
メモリ許可の待機時間は有限です。 過剰な待機 (たとえば、20 分以上) が経過すると、SQL Server はクエリをタイムアウトし、エラー 8645 "メモリ リソースがクエリを実行するのを待っている間にタイムアウトが発生しました。 クエリを再実行してください。sys.dm_exec_query_memory_grantsのtimeout_secを見ると、サーバー レベルで設定されたタイムアウト値が表示される場合があります。 タイムアウト値は、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 つの要求にメモリが付与され、他の 20 個の要求が許可を待機していることを示しています。 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_resource_semaphoresによって提供されるサーバー レベルの統計情報に加えて、sys.dm_exec_requestsとsys.dm_exec_query_memory_grantsを介してセッションとその許可をより詳細に確認できます。
Note
このクエリでは、 sys.dm_exec_query_resource_semaphores を使用するため、セッションごとに 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 )
Note
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 |
サンプル出力は、 session_id = 60 によって送信されたクエリが、要求された 9 MB のメモリ許可を正常に取得したが、クエリの実行を正常に開始するために必要なのは 7 MB だけだった方法を明確に示しています。 最後に、クエリはサーバーから受信した 9 MB のうち 1 MB のみを使用しました。 出力には、セッション 75 と 86 がメモリ許可を待機しているため、 RESOURCE_SEMAPHORE wait_typeも示されます。 待機時間は 1,300 秒 (21 分) を超え、 granted_memory_mb は NULL。
この診断クエリはサンプルであるため、ニーズに合わせて自由に変更できます。 このクエリのバージョンは、Microsoft SQL Server サポートが使用する診断ツールでも使用されます。
診断ツール
Microsoft SQL Server テクニカル サポートがログを収集し、問題をより効率的にトラブルシューティングするために使用する診断ツールがあります。 SQL LogScout および Pssdiag Configuration Manager (SQLDiag と共に) は、メモリ許可の問題の診断に役立つ前述の DMV およびパフォーマンス モニター カウンターの出力を収集します。
sql LogScout を LightPerf、 GeneralPerf、または DetailedPerf シナリオで実行すると、ツールは必要なログを収集します。 その後、YourServer_PerfStats.out を手動で調べて、 -- dm_exec_query_resource_semaphores -- と -- dm_exec_query_memory_grants -- 出力を探すことができます。 または、手動検査の代わりに、 SQL Nexus を使用して、SQL LogScout または PSSDIAG からの出力を SQL Server データベースにインポートできます。 SQL Nexus は、メモリ許可の診断に必要な情報を含む、 tbl_dm_exec_query_resource_semaphores と tbl_dm_exec_query_memory_grantsの 2 つのテーブルを作成します。 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演算子を使用します。Merge Join 演算子は、クエリ オプティマイザーによって選択された場合、両方の結合された入力を並べ替える必要があります。 いずれかのテーブルの結合列でクラスター化インデックスを使用できない場合、SQL Server によって並べ替えがトリガーされる場合があります。
クエリで HASH クエリ プラン演算子が使用される理由:
この一覧は網羅的ではありませんが、ハッシュ操作の最も一般的に発生する理由が含まれています。 クエリ プランを分析 ハッシュ一致操作を識別します。
JOIN (T-SQL): テーブルを結合する場合、SQL Server には、
Nested Loop、Merge Join、Hash Joinの 3 つの物理演算子から選択できます。 SQL Server が Hash Join を選択する場合は、中間結果を格納して処理するために QE メモリが必要になります。 通常、インデックスが適切でないことが、この最もリソースコストの高い結合演算子 (Hash Join) につながる可能性があります。 クエリ プランを指定してHash Matchを識別するには、論理演算子と物理演算子のリファレンスを参照してください。DISTINCT (T-SQL):
Hash Aggregate演算子を使用して、行セット内の重複をなくすことができます。 クエリ プランでHash Match(Aggregate) を検索するには、「 論理演算子と物理演算子のリファレンスを参照してください。UNION (T-SQL): これは
DISTINCTに似ています。Hash Aggregateを使用して、この演算子の重複を削除できます。SUM/AVG/MAX/MIN (T-SQL): 集計操作は、
Hash Aggregateとして実行される可能性があります。 クエリ プランでHash Match(Aggregate) を検索するには、「 論理演算子と物理演算子のリファレンスを参照してください。
これらの一般的な理由を知ることは、SQL Server に送信される大きなメモリ許可要求を可能な限り排除するのに役立ちます。
並べ替え操作とハッシュ操作または許可サイズを減らす方法
- 統計を最新の状態に保ちます。 この基本的な手順により、多くのレベルのクエリのパフォーマンスが向上し、クエリ プランを選択するときにクエリ オプティマイザーに最も正確な情報が確実に含まれるようにします。 SQL Server は、統計に基づいてメモリ許可を要求するサイズを決定します。 古い統計は、許可要求の過大評価または過小評価を引き起こす可能性があるため、不必要に高い許可要求が発生したり、結果がディスクにスピルされたりする可能性があります。 自動更新統計がデータベースで有効になっていることを確認するか、UPDATE STATISTICSまたはsp_updatestatsで静的な統計を更新したままにします。
- テーブルからの行数を減らします。 より制限の厳しい WHERE フィルターまたは JOIN を使用して行数を減らすと、クエリ プラン内の後続の並べ替えにより、小さな結果セットの順序付けまたは集計が行われます。 中間結果セットが小さいほど、ワーキング セットのメモリが少なくなります。 これは、作業セットのメモリを節約するだけでなく、CPU と I/O を削減するために開発者が従うことができる一般的なルールです (この手順は常に可能であるとは限りません)。 適切に記述されたリソース効率の高いクエリが既に設定されている場合は、このガイドラインが満たされています。
- 結合列にインデックスを作成して、結合を支援します。 クエリ プランの中間操作は、基になるテーブルのインデックスの影響を受けます。 たとえば、テーブルに結合列のインデックスがなく、マージ結合が最もコスト効率の高い結合演算子であることがわかった場合、結合を実行する前に、そのテーブルのすべての行を並べ替える必要があります。 代わりに、列にインデックスが存在する場合は、並べ替え操作を削除できます。
- ハッシュ操作を回避するためにインデックスを作成します。 一般に、基本的なクエリ チューニングでは、読み取りを減らし、可能な限り大規模な並べ替えやハッシュ操作を最小限に抑えたり排除したりするのに役立つ適切なインデックスがクエリに存在するかどうかを確認することから始まります。 ハッシュ結合は、通常、大きい、並べ替えられていない、インデックスのない入力を処理するために選択されます。 インデックスを作成すると、このオプティマイザー戦略が変更され、データの取得が高速化される可能性があります。 インデックスの作成については、インデックス候補が見つからないデータベース エンジン チューニング アドバイザーと非クラスター化インデックスの調整に関するを参照してください。
GROUP BYを使用する集計クエリに適した場合は、COLUMNSTORE インデックスを使用します。 非常に大きな行セットを処理し、通常は "グループ化" 集計を実行する分析クエリでは、作業を完了するために大きなメモリ チャンクが必要になる場合があります。 順序付けされた結果を提供するインデックスが使用できない場合、並べ替えはクエリ プランに自動的に導入されます。 非常に大きな結果の一種は、高価なメモリ許可につながる可能性があります。ORDER BYが不要な場合は削除します。 結果が独自の方法で結果を並べ替えたり、ユーザーが表示されるデータの順序を変更できるアプリケーションに結果がストリーミングされる場合は、SQL Server 側で並べ替えを実行する必要はありません。 サーバーが生成する順序でデータをアプリケーションにストリーミングし、エンド ユーザーが自分で並べ替えるだけです。 Power BI や Reporting Services などのレポート アプリケーションは、エンド ユーザーがデータを並べ替えられるようにするそのようなアプリケーションの例です。- T-SQL クエリに結合が存在する場合は、慎重に LOOP JOIN ヒントを使用することを検討してください。 この手法では、メモリ許可を使用するハッシュ結合またはマージ結合が回避される場合があります。 ただし、このオプションは、結合を強制するとクエリが大幅に遅くなる可能性があるため、最後の手段としてのみ推奨されます。 ワークロードをストレス テストして、これがオプションであることを確認します。 場合によっては、入れ子になったループ結合がオプションでなくてもかまいません。 この場合、SQL Server はエラー MSSQLSERVER_8622"クエリ プロセッサは、このクエリで定義されているヒントのためにクエリ プランを生成できませんでした" で失敗する可能性があります。
メモリ許可クエリ ヒント
SQL Server 2012 SP3 以降、クエリ ヒントが存在し、クエリごとにメモリ許可のサイズを制御できます。 このヒントを使用する方法の例を次に示します。
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
ここでは保守的な値を使用することをお勧めします。特に、クエリの多くのインスタンスが同時に実行されると予想される場合に使用することをお勧めします。 運用環境に合わせてワークロードをストレス テストし、使用する値を決定します。
詳細については、「 MAX_GRANT_PERCENTとMIN_GRANT_PERCENT」を参照してください。
リソース ガバナー
QE メモリは、 MIN_MEMORY_PERCENTとMAX_MEMORY_PERCENT の設定を使用するときにリソース ガバナーが実際に制限するメモリです。 大規模なメモリ許可を引き起こすクエリを特定したら、セッションまたはアプリケーションで使用されるメモリを制限できます。 default ワークロード グループでは、SQL Server インスタンスで許可できる最大 25% のメモリをクエリで使用できることに注目してください。 詳細については、「 Resource Governor Resource Pools および CREATE WORKLOAD GROUP」を参照してください。
アダプティブ クエリ処理とメモリ許可フィードバック
SQL Server 2017 では、メモリ許可フィードバック機能が導入されました。 これにより、クエリ実行エンジンは、以前の履歴に基づいてクエリに与えられた許可を調整できます。 目標は、可能であれば許可のサイズを小さくするか、より多くのメモリが必要なときにそれを増やすことです。 この機能は、次の 3 つのウェーブでリリースされています。
- SQL Server 2017 でのバッチ モード メモリ許可フィードバック
- SQL Server 2019 の行モード メモリ許可フィードバック
- SQL Server 2022 のクエリ ストアとパーセンタイル許可を使用したディスク上のメモリ許可フィードバックの永続化
詳細については、「メモリ許可フィードバック」を参照してください。 メモリ許可機能を使用すると、実行時にクエリのメモリ許可のサイズが小さくなるため、大規模な許可要求に起因する問題が軽減される可能性があります。 この機能は、特に SQL Server 2019 以降のバージョン (行モードのアダプティブ処理が利用できる) では、クエリの実行に起因するメモリの問題に気付かない場合もあります。 ただし、この機能が (既定でオンになっている) 場合でも、QE メモリの消費量が大きい場合は、前に説明した手順を適用してクエリを書き換えます。
SQL Server または OS メモリを増やす
クエリの不要なメモリ許可を減らす手順を実行した後、関連するメモリ不足の問題が引き続き発生する場合は、ワークロードにより多くのメモリが必要となる可能性があります。 そのため、システムに十分な物理メモリがある場合は、 max server memory 設定を使用して SQL Server のメモリを増やすことを検討してください。 OS やその他のニーズに合わせてメモリの約 25% を残す場合の推奨事項に従ってください。 詳細については、「 Server memory configuration options」を参照してください。 システムで十分なメモリが使用できない場合は、物理 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 メモリを増やします。