sys.dm_exec_query_memory_grants (Transact-SQL)

發行項
07/05/2024

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

傳回所有已要求且正在等候記憶體授與或已獲得記憶體授與之查詢的相關信息。不需要記憶體授與的查詢不會出現在此檢視中。例如，排序和哈希聯結作業具有查詢執行的記憶體授與，而沒有子句的 ORDER BY 查詢則不會有記憶體授與。

在 Azure SQL Database 中，動態管理檢視不可以公開可能會影響資料庫內含項目的資訊，或公開有關使用者可存取之其他資料庫的資訊。為了避免公開這項資訊，會篩選出包含不屬於已連線租使用者之數據的每個數據列。此外，會篩選、wait_order、 pool_idgroup_id 資料行中的scheduler_id值;數據行值會設定為 NULL。

注意

若要從 Azure Synapse Analytics 或 Analytics Platform System （PDW）呼叫此專案，請使用名稱 sys.dm_pdw_nodes_exec_query_memory_grants。 Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

數據行名稱	Data type	說明
session_id	smallint	執行此查詢之會話的標識碼（SPID）。
request_id	int	要求的識別碼。會話內容中是唯一的。
scheduler_id	int	排程此查詢之排程器的標識碼。
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	此查詢正在等候之資源旗號的非唯一標識碼。注意：此標識碼在 SQL Server 2008 （10.0.x）之前的 SQL Server 版本中是唯一的。這項變更可能會影響查詢執行的疑難解答。如需詳細資訊，請參閱本文稍後的一節。
queue_id	smallint	此查詢等候記憶體授與的等候佇列標識碼。如果已授與記憶體，則為 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	執行此查詢之工作負載群組的標識碼。
pool_id	int	此工作負載群組所屬之資源集區的標識碼。
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 Database
used_worker_count	bigint	目前使用的背景工作線程數目。適用於：SQL Server (從 SQL Server 2016 (13.x) 開始) 和 Azure SQL Database
max_used_worker_count	bigint	最多使用到此刻的背景工作線程數目上限。適用於：SQL Server (從 SQL Server 2016 (13.x) 開始) 和 Azure SQL Database
reserved_node_bitmap	bigint	保留背景工作線程之 NUMA 節點的點陣圖。適用於：SQL Server (從 SQL Server 2016 (13.x) 開始) 和 Azure SQL Database

權限

在 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）開始，每個集區的行為就像是小型獨立伺服器實例，而且需要兩個旗號。從 sys.dm_exec_query_resource_semaphores 傳回的數據列數目最多可以超過 SQL Server 2005 中傳回的數據列數（9.x）。

範例

查詢逾時的典型偵錯案例可能會調查下列情況：

使用 sys.dm_os_memory_clerks、 sys.dm_os_sys_info和各種性能計數器檢查整體系統記憶體狀態。
檢查中sys.dm_os_memory_clerks是否有查詢執行記憶體保留。type = 'MEMORYCLERK_SQLQERESERVATIONS'
使用檢查等候¹ 的查詢是否有授與 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 Showplan，並從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_memory_grants (Transact-SQL)

權限

SQL Server 2022 和更新版本的權限

備註

範例

另請參閱

意見反應

其他資源