sys.dm_exec_query_memory_grants (Transact-SQL)

適用対象: SQL Server Azure SQL Database 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を使用して¹の許可を待機しているクエリを確認します。

  --Find all queries waiting in the memory queue  
  SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;
  

  ¹ このシナリオの場合、待機の種類は一般的に 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
  

関連項目

• sys.dm_exec_query_resource_semaphores (Transact-SQL)
• sys.dm_os_wait_stats (Transact-SQL)
• 回関連の動的管理ビューおよび関数 （Transact-SQL）
• スレッドおよびタスクのアーキテクチャ ガイド
• MSSQLSERVER_701
• Azure SQL Database でのメモリ不足エラーのトラブルシューティング