sys.dm_exec_query_memory_grants (Transact-SQL)

發行項
08/21/2023

適用於：SQL Server Azure SQL Database 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 和更新版本的權限

備註

範例

另請參閱

意見反應

意見反應

其他資源