sys.dm_exec_query_memory_grants (Transact-SQL)

適用対象:Database Azure Synapse Analytics Analytics Platform System (PDW) Azure SQL SQL Server (サポートされているすべてのバージョン)

メモリ許可を要求し、メモリ許可を待機しているすべてのクエリ、またはメモリ許可が与えられているすべてのクエリに関する情報を返します。 メモリ許可を必要としないクエリは、このビューには表示されません。 たとえば、並べ替え操作とハッシュ結合操作にはクエリ実行のメモリ許可があり、句のない ORDER BY クエリにはメモリ許可がありません。

Azure SQL Database では、動的管理ビューでは、データベースの包含に影響を与える情報を公開したり、ユーザーがアクセスできる他のデータベースに関する情報を公開したりすることはできません。 この情報が公開されないように、接続されているテナントに属していないデータを含む行はすべて除外されます。さらに、列 scheduler_id(、wait_orderpool_idgroup_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 このクエリを実行するために必要な最小メモリ (キロバイト単位)。 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 は、2008 年SQL Serverより前のバージョンの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) このクエリ プランの識別子。 実際の XML プランを抽出するために使用 sys.dm_exec_query_plan します。
sql_handle varbinary(64) このクエリの Transact-SQL テキストの識別子。 実際の Transact-SQL テキストを取得するために使用 sys.dm_exec_sql_text します。
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 データベース 上では、データベース内の VIEW DATABASE STATE アクセス許可が必要です。

注釈

含まれる ORDER BY 動的管理ビューまたは集計ビューを使用するクエリでは、メモリ消費が増加し、トラブルシューティング中の問題に寄与する可能性があります。

データベース管理者は、リソース ガバナー機能を使用することで、サーバー リソースを最大 64 個までのリソース プールに分散できます。 SQL Server 2008 以降では、各プールは小さな独立したサーバー インスタンスのように動作し、2 つのセマフォを必要とします。 返されるsys.dm_exec_query_resource_semaphores行の数は、SQL Server 2005 (9.x) で返される行の最大 20 倍になります。

クエリタイムアウトの一般的なデバッグ シナリオでは、次の情報を調査できます。

  • sys.dm_os_memory_clerkssys.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  
    
  • 暴走クエリが疑われる場合は、列の query_plan Showplan をsys.dm_exec_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
    

関連項目