sys.dm_exec_query_memory_grants (Transact-SQL)
適用対象: 
SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
メモリ許可を要求し、メモリの許可を待機している、またはメモリ許可が与えられているすべてのクエリに関する情報を返します。 メモリ許可を必要としないクエリは、このビューには表示されません。 たとえば、並べ替え操作とハッシュ結合操作にはクエリ実行用のメモリ許可があり、 ORDER BY 句を持たないクエリにはメモリ許可がありません。
Azure SQL Database では、動的管理ビューでは、データベースの包含に影響する情報を公開したり、ユーザーがアクセスできる他のデータベースに関する情報を公開したりすることはできません。 この情報が公開されないようにするために、接続されているテナントに属していないデータを含む行はすべて除外されます。さらに、列 scheduler_id、 wait_order、 pool_id、 group_id の値がフィルター処理されます。列の値は NULL に設定されます。
Note
これを Azure Synapse Analytics または Analytics Platform System (PDW) から呼び出すには、 sys.dm_pdw_nodes_exec_query_memory_grantsという名前を使用します。 この構文は、Azure Synapse Analytics のサーバーレス SQL プールでサポートされていません。
| 列名 | データの種類 | 説明 |
|---|---|---|
| session_id | smallint | このクエリが実行されているセッションの ID (SPID)。 |
| request_id | int | 要求の ID。 セッションのコンテキスト内で一意です。 |
| scheduler_id | int | このクエリをスケジュールしているスケジューラの ID。 |
| dop | smallint | このクエリの並列処理の次数。 |
| request_time | datetime | このクエリがメモリ許可を要求した日付と時刻。 |
| grant_time | datetime | このクエリにメモリが付与された日時。 メモリがまだ許可されていない場合は NULL です。 |
| requested_memory_kb | bigint | 要求されたメモリの合計量 (KB 単位)。 |
| granted_memory_kb | bigint | 実際に付与されたメモリの合計量 (KB 単位)。 メモリがまだ付与されていない場合は NULL にすることができます。 一般的な状況では、この値は requested_memory_kbと同じである必要があります。 インデックス作成では、最初に許可されたメモリ量を超えて、追加のオンデマンド メモリが許可される場合があります。 |
| required_memory_kb | bigint | このクエリを実行するために必要な最小メモリ (KB 単位)。 requested_memory_kb が同じか、この量より大きい。 |
| used_memory_kb | bigint | この時点で使用されている物理メモリ (KB 単位)。 |
| max_used_memory_kb | bigint | この時点までに使用された最大物理メモリ (KB 単位)。 |
| query_cost | float | 推定クエリ コスト。 |
| timeout_sec | int | このクエリがメモリ許可要求をやめるまでのタイムアウト (秒単位)。 |
| resource_semaphore_id | smallint | このクエリが待機しているリソース セマフォの非一意の ID。 注: この ID は、SQL Server 2008 (10.0.x) より前のバージョンの SQL Server で一意です。 この変更は、クエリの実行のトラブルシューティングに影響する可能性があります。 詳細については、この記事で後述する「解説」セクションを参照してください。 |
| queue_id | smallint | このクエリがメモリ許可を待機している待機キューの ID。 メモリが既に許可されている場合は NULL。 |
| wait_order | int | 指定した queue_id内の待機クエリの順番。 この値は、他のクエリでメモリ許可またはタイムアウトが取得された場合に、特定のクエリに対して変更される可能性があります。メモリが既に許可されている場合は NULL。 |
| is_next_candidate | bit | 次のメモリ許可の候補。 1 = はい 0 = いいえ NULL = メモリは既に付与されています。 |
| wait_time_ms | bigint | 待機時間 (ミリ秒単位)。 メモリが既に許可されている場合は NULL。 |
| plan_handle | varbinary(64) | このクエリ プランの識別子。 sys.dm_exec_query_planを使用して、実際の XML プランを抽出します。 |
| sql_handle | varbinary(64) | このクエリの Transact-SQL テキストの識別子。 sys.dm_exec_sql_textを使用して、実際の Transact-SQL テキストを取得します。 |
| group_id | int | このクエリが実行されているワークロード グループの ID。 |
| pool_id | int | このワークロード グループが属するリソース プールの ID。 |
| is_small | tinyint | 1 に設定すると、この許可で小さなリソース セマフォが使用されます。 0 に設定すると、通常のセマフォが使用されることを示します。 |
| ideal_memory_kb | bigint | すべて物理メモリに収まるようにメモリ許可のサイズ (KB 単位)。 これはカーディナリティの見積もりに基づいています。 |
| pdw_node_id | int | このディストリビューションがオンになっているノードの識別子。 適用対象: Azure Synapse Analytics、Analytics Platform System (PDW) |
| reserved_worker_count | bigint | 予約された ワーカー スレッドの数。 適用対象: SQL Server (開始値 SQL Server 2016 (13.x)) および Azure SQL データベース |
| used_worker_count | bigint | この時点で使用ワーカー スレッドの数。 適用対象: SQL Server (開始値 SQL Server 2016 (13.x)) および Azure SQL データベース |
| max_used_worker_count | bigint | ワーカー スレッドの最大数この時点まで使用されます。 適用対象: SQL Server (開始値 SQL Server 2016 (13.x)) および Azure SQL データベース |
| reserved_node_bitmap | bigint | ワーカー スレッドが予約されている NUMA ノードのビットマップ。 適用対象: SQL Server (開始値 SQL Server 2016 (13.x)) および Azure SQL データベース |
アクセス許可
SQL Server では、VIEW SERVER STATE 権限が必要です。
Azure SQL Database では、データベースにおける VIEW DATABASE STATE アクセス許可が必要です。
SQL Server 2022 以降でのアクセス許可
サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。
解説
ORDER BYまたは集計を含む動的管理ビューを使用するクエリでは、メモリ消費量が増加し、トラブルシューティングの問題に寄与する可能性があります。
リソース ガバナー機能を使用すると、データベース管理者は、最大 64 個のプールを含むリソース プール間でサーバー リソースを分散できます。 SQL Server 2008 (10.0.x) 以降では、各プールは小さな独立したサーバー インスタンスのように動作し、2 つのセマフォを必要とします。 sys.dm_exec_query_resource_semaphoresから返される行の数は、SQL Server 2005 (9.x) で返される行の最大 20 倍になります。
例
クエリタイムアウトの一般的なデバッグ シナリオでは、次を調査できます。
sys.dm_os_memory_clerks、sys.dm_os_sys_info、さまざまなパフォーマンス カウンターを使用して、システム メモリの全体的な状態を確認します。
type = 'MEMORYCLERK_SQLQERESERVATIONS'sys.dm_os_memory_clerksクエリ実行メモリ予約を確認します。sys.dm_exec_query_memory_grantsを使用して1の許可を待機しているクエリを確認します。--Find all queries waiting in the memory queue SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;1 このシナリオの場合、待機の種類は一般的に RESOURCE_SEMAPHORE になります。 詳しくは「sys.dm_os_wait_stats (Transact-SQL)」をご覧ください。
sys.dm_exec_cached_plans (Transact-SQL)とsys.dm_exec_query_plan (Transact-SQL) を使用してメモリ許可を持つクエリのキャッシュを検索する
-- retrieve every query plan from the plan cache USE master; GO SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); GOランナウェイ クエリが疑われる場合は、sys.dm_exec_query_planから
query_plan列のプラン表示を調べ、sys.dm_exec_sql_textからバッチtextをクエリします。 sys.dm_exec_requestsを使用して、現在実行中のメモリ集中型クエリをさらに調べます。--Active requests with memory grants SELECT --Session data s.[session_id], s.open_transaction_count --Memory usage , r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb --Query , query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count --Session history and status , s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status --Session connection information , s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_requests AS r ON r.[session_id] = s.[session_id] LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg ON mg.[session_id] = s.[session_id] OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp WHERE mg.granted_memory_kb > 0 ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc; GO