sys.dm_exec_query_memory_grants (Transact-SQL)

  • Articolo

Si applica a: SQL Server Azure SQL DatabaseIstanza gestita di SQL di Azure Azure SynapseAnalytics AnalyticsPlatform System (PDW)

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

In Azure SQL Database, le visualizzazioni di gestione dinamica non possono esporre informazioni che potrebbero influire sul contenimento del database o esporre informazioni su altri database a cui l'utente può accedere. 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, group_id vengono filtrati. Il valore della colonna è impostato su NULL.

Nota

Per chiamare questa operazione da Azure Synapse Analytics o Analytics 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 delle query in attesa all'interno dell'oggetto specificato queue_id. Questo valore può cambiare per una determinata query se altre query ottengono le concessioni di memoria o il 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 per il 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 si trova questa distribuzione.

Si applica a: Azure Synapse Analytics, Analytics Platform System (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 Azure SQL database
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 Azure SQL database
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 Azure SQL database
reserved_node_bitmap bigint Bitmap dei nodi NUMA in cui i thread di lavoro sono riservati.

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

Autorizzazioni

In SQL Server è necessaria VIEW SERVER STATE l'autorizzazione.
In Azure SQL database è necessaria l'autorizzazione VIEW DATABASE STATE nel database.

Autorizzazioni per SQL Server 2022 e versioni successive

Richiede l'autorizzazione VIEW SERVER PERFORMANCE STATE nel server.

Commenti

Le query che usano viste di 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 un'istanza di server indipendente di piccole dimensioni 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).

Esempio

Uno scenario di debug tipico per il timeout delle query può analizzare 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 in sys.dm_os_memory_clerks cui 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 cache per 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 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