sys.dm_exec_query_profiles (Transact-SQL)

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

Monitora lo stato di avanzamento delle query in tempo reale mentre la query è in esecuzione. Usare, ad esempio, questa DMV per determinare la parte della query la cui esecuzione è lenta. Creare un join di questa DMV ad altre DMV di sistema utilizzando le colonne identificate nel campo descrizione. In alternativa, creare un join di questa DMV con altri contatori di prestazioni, ad esempio Performance Monitor, xperf, usando le colonne di tipo timestamp.

Tabella restituita

I contatori restituiti sono specifici per ogni operatore per ogni thread. I risultati sono dinamici e non corrispondono ai risultati delle opzioni esistenti, SET STATISTICS XML ON ad esempio che creano output solo al termine della query.

Nome colonna Tipo di dati Descrizione
session_id smallint Identifica la sessione in cui viene eseguita la query. Fa riferimento a dm_exec_sessions.session_id.
request_id int Identifica la richiesta di destinazione. Fa riferimento a dm_exec_sessions.request_id.
sql_handle varbinary(64) È un token che identifica in modo univoco il batch o la stored procedure di cui fa parte la query. Fa riferimento a dm_exec_query_stats.sql_handle.
plan_handle varbinary(64) Token che identifica in modo univoco un piano di esecuzione di query per un batch eseguito e il relativo piano risiede nella cache dei piani o è attualmente in esecuzione. Riferimenti dm_exec_query_stats.plan_handle.
physical_operator_name nvarchar(256) Nome dell'operatore fisico.
node_id int Identifica un nodo operatore nell'albero della query.
thread_id int Distingue i thread (per una query parallela) che appartengono allo stesso nodo operatore della query.
task_address varbinary(8) Identifica l'attività SQLOS utilizzata da questo thread. Fa riferimento a dm_os_tasks.task_address.
row_count bigint Numero di righe restituite finora dall'operatore.
rewind_count bigint Numero di ripristini finora.
rebind_count bigint Numero di riassociazioni finora.
end_of_scan_count bigint Numero di analisi terminate finora.
estimate_row_count bigint Numero stimato di righe. Può essere utile per confrontare il valore estimated_row_count con il valore row_count effettivo.
first_active_time bigint Ora, in millisecondi, in cui l'operatore è stato chiamato la prima volta.
last_active_time bigint Ora, in millisecondi, in cui l'operatore è stato chiamato l'ultima volta.
open_time bigint Timestamp apertura in millisecondi.
first_row_time bigint Timestamp in cui è stata aperta la prima riga in millisecondi.
last_row_time bigint Timestamp in cui è stata aperta l'ultima riga in millisecondi.
close_time bigint Timestamp chiusura in millisecondi.
elapsed_time_ms bigint Tempo totale trascorso (in millisecondi) usato finora dalle operazioni del nodo di destinazione.
cpu_time_ms bigint Tempo totale cpu (in millisecondi) usato dalle operazioni del nodo di destinazione finora.
database_id smallint ID del database contenente l'oggetto in cui vengono eseguite le letture e le scritture.
object_id int Identificatore dell'oggetto in cui vengono eseguite le letture e le scritture. Fa riferimento a sys.objects.object_id.
index_id int Indice in cui viene aperto il set di righe.
scan_count bigint Numero di analisi tabella/indice.
logical_read_count bigint Numero di letture logiche.
physical_read_count bigint Numero di letture fisiche.
read_ahead_count bigint Numero di letture anticipate.
write_page_count bigint Numero di scritture di pagina a causa dello spill.
lob_logical_read_count bigint Numero di letture logiche LOB.
lob_physical_read_count bigint Numero di letture fisiche LOB.
lob_read_ahead_count bigint Numero di letture anticipate LOB.
segment_read_count int Numero di letture anticipate di segmenti.
segment_skip_count int Numero di segmenti ignorati finora.
actual_read_row_count bigint Numero di righe lette da un operatore prima dell'applicazione del predicato residuo.
estimated_read_row_count bigint Si applica a: a partire da SQL Server 2016 (13.x) SP1.
Numero di righe stimate da leggere da un operatore prima dell'applicazione del predicato residuo.

Osservazioni generali

Se il nodo del piano di query non dispone di operazioni di I/O, tutti i contatori correlati all'I/O sono impostati su NULL.

I contatori correlati all'I/O segnalati da questa DMV sono più granulari di quelli segnalati da SET STATISTICS IO nei due modi seguenti:

  • SET STATISTICS IO raggruppa i contatori per tutte le operazioni di I/O in una determinata tabella. Con questa DMV si otterranno contatori separati per ogni nodo nel piano di query che esegue operazioni di I/O nella tabella.

  • In caso di analisi parallela, questa DMV restituisce i contatori per ogni thread parallelo usato nell'analisi.

A partire da SQL Server 2016 (13.x) SP1, l'infrastruttura di profilatura delle statistiche di esecuzione delle query standard esiste side-by-side con un'infrastruttura di profilatura leggera delle statistiche di esecuzione delle query. SET STATISTICS XML ONe SET STATISTICS PROFILE ON usano sempre l'infrastruttura di profilatura delle statistiche di esecuzione delle query standard. Per sys.dm_exec_query_profiles essere popolato, è necessario abilitare una delle infrastrutture di profilatura delle query. Per altre informazioni, vedere Infrastruttura di profilatura query.

Nota

La query sottoposta a indagine deve essere avviata dopo l'abilitazione dell'infrastruttura di profilatura delle query, abilitandola dopo l'avvio della query non produrrà risultati in sys.dm_exec_query_profiles. Per altre informazioni su come abilitare le infrastrutture di profilatura delle query, vedere Infrastruttura di profilatura query.

Autorizzazioni

  • In SQL Server e Istanza gestita di SQL di Azure è richiesta VIEW DATABASE STATE l'autorizzazione e l'appartenenza al ruolo del db_owner database.
  • Per database SQL di Azure livelli Premium, è necessaria l'autorizzazione VIEW DATABASE STATE nel database.
  • In database SQL di Azure obiettivi di servizio Basic, S0 e S1 e per i database nei pool elastici, è necessario l'account amministratore del server o l'account amministratore di Microsoft Entra. Per tutti gli altri obiettivi di servizio database SQL, l'autorizzazione VIEW DATABASE STATE è necessaria nel database.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW DATABASE PERFORMANCE STATE per il database.

Esempi

Passaggio 1: Accedere a una sessione in cui si prevede di eseguire la query che verrà analizzata con sys.dm_exec_query_profiles. Per configurare la query per la profilatura, usare SET STATISTICS PROFILE ON. Eseguire la query in questa stessa sessione.

--Configure query for profiling with sys.dm_exec_query_profiles  
SET STATISTICS PROFILE ON;  
GO  

--Or enable query profiling globally under SQL Server 2016 SP1 or above (not needed in SQL Server 2019)  
DBCC TRACEON (7412, -1);  
GO 
  
--Next, run your query in this session, or in any other session if query profiling has been enabled globally

Passaggio 2: Accedere a una seconda sessione diversa dalla sessione in cui è in esecuzione la query.

L'istruzione seguente riepiloga lo stato di avanzamento della query attualmente in esecuzione nella sessione 54. A tale scopo, viene calcolato il numero totale di righe di output restituite da tutti i thread per ogni nodo e confrontato con il numero stimato di righe di output per tale nodo.

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT node_id,physical_operator_name, SUM(row_count) row_count, 
  SUM(estimate_row_count) AS estimate_row_count, 
  CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)  
FROM sys.dm_exec_query_profiles   
WHERE session_id=54
GROUP BY node_id,physical_operator_name  
ORDER BY node_id;

Vedi anche

Funzioni a gestione dinamica e DMV (Transact-SQL)
Funzioni e viste a gestione dinamica relative all'esecuzione (Transact-SQL)