Condividi tramite

sys.dm_exec_query_memory_grants (Transact-SQL)

  • Articolo

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Restituisce informazioni su tutte le query richieste e in attesa di una concessione di memoria o che hanno ricevuto 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 hanno concessioni di memoria per l'esecuzione di query, mentre le query senza una ORDER BY clausola non avranno una concessione di memoria.

Nel 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 contenente dati che non appartengono al tenant connesso viene filtrata. Inoltre, i valori nelle colonne scheduler_id, wait_orderpool_id, vengono group_id filtrati. Il valore della colonna è impostato su NULL.

Nota

Per chiamare questa operazione da Azure Synapse Analytics o da Platform System (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. Per una situazione tipica, questo valore deve essere uguale requested_memory_kba . 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 (10.0.x). 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 di 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 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. Utilizzare sys.dm_exec_query_plan per estrarre il piano XML effettivo.
sql_handle varbinary(64) Identificatore del testo Transact-SQL per questa 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 in cui è attiva la distribuzione.

Si applica a: Azure Synapse Analytics, Piattaforma di analisi (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 è richiesta VIEW SERVER STATE l'autorizzazione.
Nel database SQL di Azure è richiesta l'autorizzazione VIEW DATABASE STATE.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Osservazioni:

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

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 (10.0.x), ogni pool si comporta come una piccola istanza del 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 alle righe restituite in SQL Server 2005 (9,x).

Esempi

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

  • Controllare lo stato complessivo della memoria del sistema usando sys.dm_os_memory_clerks, sys.dm_os_sys_info e vari contatori delle prestazioni.

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

  • Verificare la presenza di query in attesadi 1 per le concessioni usando 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 in esecuzione, esaminare showplan nella query_plan colonna da sys.dm_exec_query_plan e eseguire query 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