Infrastruttura di profilatura query

Si applica a: SQL Server Database SQL di Azure

Il motore di database SQL Server offre la possibilità di accedere alle informazioni di runtime nei piani di esecuzione delle query. Una delle azioni più importanti quando si verifica un problema di prestazioni consiste nell'individuare in modo preciso il carico di lavoro in esecuzione e la modalità di gestione delle risorse. Per questa ragione, è necessario accedere al piano di esecuzione effettivo.

Sebbene il completamento della query sia un prerequisito per la disponibilità di un piano di query effettivo, le statistiche di query dinamiche possono offrire informazioni in tempo reale sul processo di esecuzione della query durante il passaggio dei dati da un operatore del piano di query all'altro. Il piano dinamico delle query visualizza lo stato complessivo delle query e le statistiche di esecuzione a livello di operatore, ad esempio il numero di righe prodotte, il tempo trascorso, lo stato di avanzamento dell'operatore e così via. Poiché questi dati sono disponibili in tempo reale senza dover attendere il completamento della query, queste statistiche di esecuzione sono estremamente utili per il debug di problemi relativi alle prestazioni delle query, come le query a esecuzione prolungata e quelle ad esecuzione mai conclusa.

Infrastruttura di profilatura delle statistiche di esecuzione query standard

L'infrastruttura del profilo delle statistiche di esecuzione query o profilatura standard deve essere abilitata per raccogliere informazioni sui piani di esecuzione, ovvero il totale delle righe e l'utilizzo della CPU e dell'I/O. I seguenti metodi di raccolta delle informazioni sui piani di esecuzione per una sessione di destinazione usano l'infrastruttura di profilatura standard:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Statistiche sulle query dinamiche

I seguenti metodi di raccolta delle informazioni sui piani di esecuzione per tutte le sessioni usano l'infrastruttura di profilatura standard:

• Evento esteso query_post_execution_showplan. Per abilitare gli eventi estesi, vedere Monitor System Activity Using Extended Events.
• Evento traccia Showplan XML in Traccia SQL e SQL Server Profiler. Per altre informazioni sull'evento traccia, vedere Classe di evento Showplan XML.

Durante l'esecuzione di una sessione di eventi estesi che usa l'evento query_post_execution_showplan, viene popolata anche la DMV sys.dm_exec_query_profiles che abilita le statistiche di query dinamiche per tutte le sessioni usando Monitoraggio attività o eseguendo la query direttamente nella DMV. Per altre informazioni, vedere Live Query Statistics.

L’infrastruttura di profilatura delle statistiche di esecuzione query lightweight

A partire da SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x), è stata introdotta una nuova infrastruttura di profilatura delle statistiche di esecuzione delle query lightweight o profilatura lightweight.

Infrastruttura di profilatura delle statistiche di esecuzione query lightweight v1

Si applica a: SQL Server 2014 (12.x) SP2 fino a SQL Server 2016 (13.x).

A partire da SQL Server 2014 (12.x) SP2 e SQL Server 2016 (13.x), con l'introduzione della profilatura lightweight è stato ridotto l'overhead delle prestazioni per raccogliere informazioni sui piani di esecuzione. A differenza della profilatura standard, la profilatura lightweight non raccoglie informazioni di runtime della CPU. La profilatura lightweight continua comunque a raccogliere il totale di righe conteggio delle righe e le informazioni sull'utilizzo dell'I/O.

È stato anche introdotto un nuovo evento esteso query_thread_profile che usa la profilatura lightweight. Questo evento esteso espone le statistiche di esecuzione di ogni operatore offrendo informazioni più approfondite sulle prestazioni di ogni nodo e thread. È possibile configurare una sessione di esempio che usa questo evento esteso come illustrato nell'esempio che segue:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Durante l'esecuzione di una sessione di eventi estesi che usa l'evento query_thread_profile, viene popolata anche la DMV sys.dm_exec_query_profiles tramite la profilatura lightweight e vengono abilitate le statistiche di query dinamiche per tutte le sessioni usando Monitoraggio attività o eseguendo la query direttamente nella DMV.

Infrastruttura di profilatura delle statistiche di esecuzione query lightweight v2

Si applica a: SQL Server 2016 (13.x) SP1 fino a SQL Server 2017 (14.x).

SQL Server 2016 (13.x) SP1 include una versione rivista della profilatura lightweight con un overhead minimo. La profilatura lightweight può essere anche abilitata a livello globale tramite il flag di traccia 7412 per le versioni indicate nella sezione precedente Si applica a. Una nuova DMF sys.dm_exec_query_statistics_xml viene introdotta per restituire il piano di esecuzione query per le richieste in elaborazione.

A partire da SQL Server 2016 (13.x) SP2 CU3 e SQL Server 2017 (14.x) CU11, se la profilatura lightweight non è abilitata globalmente, è possibile usare il nuovo argomento QUERY_PLAN_PROFILE dell'hint per la query USE HINT per abilitare la profilatura a livello di query per qualsiasi sessione. Al termine dell'esecuzione di una query contenente questo nuovo hint, viene generato anche un nuovo evento esteso query_plan_profile che fornisce l'XML di un piano di esecuzione effettivo simile all'evento esteso query_post_execution_showplan.

Una sessione di esempio che usa l'evento esteso query_plan_profile può essere configurata come illustrato nell'esempio seguente:

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Infrastruttura di profilatura delle statistiche di esecuzione query lightweight v3

Si applica a: SQL Server, a partire da SQL Server 2019 (15.x), e Database SQL di Azure

SQL Server 2019 (15.x) e Azure SQL Database includono una nuova versione rivista della profilatura lightweight che raccoglie informazioni sul numero di righe per tutte le esecuzioni. La profilatura lightweight è abilitata per impostazione predefinita sia in SQL Server 2019 (15.x) che nel database SQL di Azure. A partire da SQL Server 2019 (15.x), questo flag di traccia 7412 non ha alcun effetto. È possibile disabilitare la profilatura lightweight a livello di database usando la configurazione con ambito database LIGHTWEIGHT_QUERY_PROFILING: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

È stata introdotta una nuova DMF sys.dm_exec_query_plan_stats per restituire l'equivalente dell'ultimo piano di esecuzione effettivo noto per la maggior parte delle query. Tale DMF è denominata statistiche dell'ultimo piano di query. È possibile abilitare le statistiche dell'ultimo piano di query a livello di database usando la configurazione con ambito database LAST_QUERY_PLAN_STATS: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Un nuovo evento esteso query_post_execution_plan_profile raccoglie l'equivalente di un piano di esecuzione effettivo in base alla profilatura leggera, a differenza di query_post_execution_showplan che usa la profilatura standard. SQL Server 2017 (14.x) offre anche questo evento a partire da CU14. È possibile configurare una sessione di esempio che usa l'evento esteso query_post_execution_plan_profile come illustrato nell'esempio seguente:

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Esempio 1 - Sessione Evento esteso con profilatura standard

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Esempio 2 - Sessione Evento esteso con profilatura leggera

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Linee guida per l'utilizzo dell'infrastruttura di profilatura di query

La tabella seguente riepiloga le azioni per abilitare la profilatura standard o la profilatura leggera, sia a livello globale (a livello di server) che in una singola sessione. Include anche la versione minima per cui è disponibile l'azione.

Osservazioni:

A partire dalla profilatura lightweight v2 con overhead ridotto, qualsiasi server che non è già basato su CPU può eseguire la profilatura lightweight in modo continuo e consentire ai professionisti di database di inserirsi in qualsiasi esecuzione in corso in qualsiasi momento, ad esempio usando Monitoraggio attività o eseguendo direttamente una query in sys.dm_exec_query_profiles e ottenere il piano di query con le statistiche di runtime.

Per altre informazioni sull'overhead delle prestazioni della profilatura d query, vedere il post di blog Developers Choice: Query progress - anytime, anywhere (Scelta degli sviluppatori: Avanzamento delle query, sempre e dovunque).

Vedi anche

Monitoraggio e ottimizzazione delle prestazioni
Strumenti per il monitoraggio e l'ottimizzazione delle prestazioni
Aprire Monitoraggio attività (SQL Server Management Studio)
Monitoraggio attività
Monitoraggio delle prestazioni tramite Query Store
Monitorare l'attività del sistema mediante gli eventi estesi
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Flag di traccia
Guida di riferimento a operatori Showplan logici e fisici
piano di esecuzione effettivo
Statistiche sulle query dinamiche