sys.dm_exec_query_memory_grants (Transact-SQL)

  • Articolo
  • 6 minuti per la lettura

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure sìAzure Synapse Analytics sìParallel Data Warehouse

Restituisce informazioni su tutte le query che hanno richiesto e sono in attesa di una concessione di memoria o a cui è stata concessa una concessione di memoria. Le query che non richiedono una concessione di memoria non verranno visualizzate in questa visualizzazione. Ad esempio, le operazioni di ordinamento e hash join dispongono di concessioni di memoria per l'esecuzione di query, ORDER BY mentre le query senza una clausola non avranno una concessione di memoria.

In database SQL di Azure, le viste a gestione dinamica non possono esporre le informazioni che influenzerebbero l'indipendenza del database o le informazioni sugli altri database a cui l'utente dispone di accesso. Per evitare di esporre queste informazioni, ogni riga che contiene dati che non appartengono al tenant connesso viene filtrata. Inoltre, i valori nelle colonne scheduler_id, wait_order, pool_id, vengono group_id filtrati. Il valore della colonna è impostato su NULL.

Nota

Per chiamare da o Azure Synapse Analytics Piattaforma di strumenti analitici (PDW), usare il nome sys.dm_pdw_nodes_exec_query_memory_grants. Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.

Nome colonna Tipo di dati Descrizione
session_id smallint ID (SPID) della sessione nella quale viene eseguita la query.
request_id int ID della richiesta. Valore univoco nel contesto della sessione.
scheduler_id int ID dell'utilità di pianificazione che sta pianificando la query.
dop smallint Grado di parallelismo della query.
request_time datetime Data e ora in cui la query ha richiesto la concessione di memoria.
grant_time datetime Data e ora in cui la memoria è stata concessa alla query. È NULL se la memoria non è stata ancora concessa.
requested_memory_kb bigint Quantità totale di memoria richiesta, espressa in kilobyte.
granted_memory_kb bigint Quantità totale di memoria effettivamente concessa, espressa in kilobyte. Può essere NULL se la memoria non è stata ancora concessa. In una situazione tipica, questo valore deve essere uguale a requested_memory_kb. In caso di creazione di indici, il server può concedere ulteriore memoria su richiesta in aggiunta alla memoria concessa inizialmente.
required_memory_kb bigint Quantità minima di memoria necessaria per l'esecuzione della query, espressa in kilobyte. requested_memory_kb è uguale o maggiore di questa quantità.
used_memory_kb bigint Memoria fisica attualmente in uso, espressa in kilobyte.
max_used_memory_kb bigint Memoria fisica massima utilizzata fino a questo momento, espressa in kilobyte.
query_cost float Costo stimato della query.
timeout_sec int Timeout in secondi prima che la query rinunci alla richiesta di concessione di memoria.
resource_semaphore_id smallint ID non univoco del semaforo di risorsa su cui la query è in attesa.

Nota: Questo ID è univoco nelle versioni di SQL Server precedenti a SQL Server 2008. La modifica può influire sulla risoluzione dei problemi relativi all'esecuzione di query. Per altre informazioni, vedere la sezione "Osservazioni" più avanti in questo articolo.
queue_id smallint ID della coda nella quale la query sta attendendo la concessione di memoria. È NULL se la memoria è già stata concessa.
wait_order int Ordine sequenziale delle query in attesa all'interno dell'oggetto specificato queue_id. Questo valore può cambiare per una determinata query se altre query ottengono concessioni di memoria o si verifica un timeout. NULL se la memoria è già concessa.
is_next_candidate bit Candidato alla concessione di memoria successiva.

1 = Sì

0 = No

NULL = Memoria già concessa
Wait_time_ms bigint Periodo di attesa espresso in millisecondi. È NULL se la memoria è già stata concessa.
plan_handle varbinary(64) Identificatore del piano di query. Usare sys.dm_exec_query_plan per estrarre il piano XML effettivo.
sql_handle varbinary(64) Identificatore del testo Transact-SQL della query. Usare sys.dm_exec_sql_text per ottenere il testo Transact-SQL effettivo.
group_id int ID per il gruppo del carico di lavoro nel quale viene eseguita la query.
pool_id int ID del pool di risorse a cui appartiene il gruppo del carico di lavoro.
is_small tinyint Se il valore è 1, questa concessione utilizza il semaforo piccolo di risorsa. Se il valore è 0, viene utilizzato un semaforo normale.
ideal_memory_kb bigint Dimensioni, in kilobyte (KB), della concessione di memoria per inserire tutto nella memoria fisica. Si basa su una stima della cardinalità.
pdw_node_id int Identificatore del nodo su cui si trova la distribuzione.

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)
reserved_worker_count bigint Numero di thread di lavoro riservati.

Si applica a: SQL Server (a partire da SQL Server 2016 (13.x)) e database SQL di Azure
used_worker_count bigint Numero di thread di lavoro usati in questo momento.

Si applica a: SQL Server (a partire da SQL Server 2016 (13.x)) e database SQL di Azure
max_used_worker_count bigint Numero massimo di thread di lavoro usati fino a questo momento.

Si applica a: SQL Server (a partire da SQL Server 2016 (13.x)) e database SQL di Azure
reserved_node_bitmap bigint Bitmap dei nodi NUMA in cui sono riservati i thread di lavoro.

Si applica a: SQL Server (a partire da SQL Server 2016 (13.x)) e database SQL di Azure

Autorizzazioni

In SQL Serverè necessaria l'autorizzazione VIEW SERVER STATE .
In database SQL di Azure è richiesta l'autorizzazione VIEW DATABASE STATE per il database.

Commenti

Le query che usano viste a gestione dinamica che includono ORDER BY aggregazioni o possono aumentare il consumo di memoria e quindi contribuire al problema che stanno risoluzionendo.

La funzionalità Resource Governor consente a un amministratore di database di distribuire risorse del server fra un massimo di 64 pool di risorse. A partire da SQL Server 2008, ogni pool si comporta come una piccola istanza di server indipendente e richiede due semafori. Il numero di righe restituite da sys.dm_exec_query_resource_semaphores può essere fino a 20 volte superiore rispetto alle righe restituite in SQL Server 2005 (9.x).

Esempio

Uno scenario di debug tipico per il timeout delle query può esaminare quanto segue:

  • Verificare lo stato complessivo della memoria di sistema utilizzando sys.dm_os_memory_clerks, sys.dm_os_sys_info e diversi contatori di prestazioni.

  • Verificare la presenza di prenotazioni di memoria per l'esecuzione di query in dove sys.dm_os_memory_clerks type = 'MEMORYCLERK_SQLQERESERVATIONS'.

  • Verificare la presenza di query in attesa1 per le concessioni tramite 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;

    1 In questo scenario il tipo di attesa è in genere RESOURCE_SEMAPHORE. Per altre informazioni, vedere sys.dm_os_wait_stats (Transact-SQL).

  • Cercare nella cache le query con concessioni di memoria usando sys.dm_exec_cached_plans (Transact-SQL) e 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

  • Se si sospetta una query runaway, esaminare showplan query_plan nella colonna da sys.dm_exec_query_plan ed eseguire query in batch text da sys.dm_exec_sql_text. Esaminare ulteriormente le query a elevato utilizzo di memoria attualmente in esecuzione, usando 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

Vedi anche