Condividi tramite

Infrastruttura di profilatura query

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric

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:

Nota

Fare clic sul pulsante Includi statistiche query dinamiche in SQL Server Management Studio per usare l'infrastruttura di profilatura standard.
Nelle versioni successive di SQL Server, se l'infrastruttura di profilatura lightweight è abilitata, verrà usata da Statistiche query dinamiche al posto della profilatura standard quando viene visualizzata tramite Monitoraggio attività o eseguendo direttamente una query sul DMV di sys.dm_exec_query_profiles.

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

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.

Nota

Le stored procedure compilate in modo nativo non sono supportate con la 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);

Nota

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).

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.

Nota

L'evento esteso query_plan_profile si avvale anche della profilatura leggera benché non venga usato l'hint per la query.

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.

Ambito Profilatura standard Profilatura leggera
Generale Sessione XEvent con XE query_post_execution_showplan ; A partire da SQL Server 2012 (11.x) Flag di traccia 7412; a partire da SQL Server 2016 (13.x) SP1
Generale Traccia SQL e SQL Server Profiler con l'evento di traccia Showplan XML; a partire da SQL Server 2000 Sessione XEvent con XE query_thread_profile ; A partire da SQL Server 2014 (12.x) SP2
Generale - Sessione XEvent con XE query_post_execution_plan_profile ; A partire da SQL Server 2017 (14.x) CU14 e SQL Server 2019 (15.x)
Sessione Usare SET STATISTICS XML ON; a partire da SQL Server 2000 Usare l'hint per la QUERY_PLAN_PROFILE query insieme a una sessione XEvent con XE query_plan_profile ; A partire da SQL Server 2016 (13.x) SP2 CU3 e SQL Server 2017 (14.x) CU11
Sessione Usare SET STATISTICS PROFILE ON; a partire da SQL Server 2000 -
Sessione Fare clic sul pulsante Statistiche query dinamiche in SSMS; a partire da SQL Server 2014 (12.x) SP2 -

Osservazioni:

Importante

A causa di una possibile violazione di accesso casuale durante l'esecuzione di una stored procedure di monitoraggio relativa a sys.dm_exec_query_statistics_xml, assicurarsi che KB 4078596 sia installato in SQL Server 2016 (13.x) e SQL Server 2017 (14.x).

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).

Nota

Se l'infrastruttura di profilatura standard è già abilitata, gli eventi estesi che sfruttano la profilatura leggera useranno le informazioni disponibili nella profilatura standard. Si supponga ad esempio che sia in esecuzione una sessione di evento esteso che usa query_post_execution_showplan e che venga avviata un'altra sessione che usa query_post_execution_plan_profile. La seconda sessione userà le informazioni provenienti dalla profilatura standard.

Nota

In SQL Server 2017 (14.x) la profilatura leggera è disattivata per impostazione predefinita, ma viene attivata quando viene avviata una traccia XEvent che si basa su query_post_execution_plan_profile e viene quindi disattivata di nuovo quando la traccia viene arrestata. Di conseguenza, se le tracce XEvent basate su query_post_execution_plan_profile vengono spesso avviate e arrestate in un'istanza di SQL Server 2017 (14.x), è consigliabile attivare la profilatura leggera a livello globale con il flag di traccia 7412 per evitare il sovraccarico ripetuto di attivazione/disattivazione.

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