Condividi tramite

Infrastruttura per la profilazione delle query

Si applica a:SQL ServerAzure SQL DatabaseAzure SQL Istanza gestitaDatabase SQL in Microsoft Fabric

Il motore di database di SQL Server consente di accedere alle informazioni di runtime sui 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. Pertanto, l'accesso al piano di esecuzione effettivo è importante.

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 delle query standard

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

Note

Se si seleziona il pulsante Includi statistiche query dinamiche in SQL Server Management Studio, viene usata l'infrastruttura di profilatura standard. Nelle versioni successive di SQL Server, se l'infrastruttura di profilatura leggera è abilitata, viene usata dalle statistiche delle query dinamiche anziché dalla profilatura standard quando viene visualizzata tramite Monitoraggio attività o eseguendo direttamente query sulla DMV sys.dm_exec_query_profiles .

I metodi seguenti per raccogliere informazioni sul piano di esecuzione a livello globale per tutte le sessioni usano l'infrastruttura di profilatura standard:

Quando si esegue una sessione eventi estesa che usa l'evento query_post_execution_showplan , viene popolata anche la DMV sys.dm_exec_query_profiles , che abilita le statistiche delle query dinamiche per tutte le sessioni, usando Monitoraggio attività o eseguendo direttamente query sulla DMV. Per altre informazioni, vedere Live Query Statistics.

L'infrastruttura leggera di profilatura delle statistiche di esecuzione delle query

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

Note

Le stored procedure compilate in modo nativo non sono supportate con la profilatura leggera.

Infrastruttura leggerissima per la profilatura delle statistiche di esecuzione delle query v1

Si applica a: DA SQL Server 2014 (12.x) SP2 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 leggera non raccoglie informazioni sul runtime della CPU. La profilatura leggera continua comunque a raccogliere il conteggio delle righe e le informazioni sull'utilizzo dell'I/O.

È stato introdotto anche un nuovo query_thread_profile evento esteso che usa la profilatura leggera. Questo evento esteso espone le statistiche di esecuzione di ogni operatore offrendo informazioni più approfondite sulle prestazioni di ogni nodo e thread. Una sessione di esempio che usa questo evento esteso può essere configurata come nell'esempio seguente:

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

Note

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

Quando si esegue una sessione eventi estesa che usa l'evento query_thread_profile , la dmv sys.dm_exec_query_profiles viene popolata anche usando la profilatura leggera, che consente statistiche sulle query dinamiche per tutte le sessioni, usando Monitoraggio attività o eseguendo direttamente query sulla DMV.

Infrastruttura leggera per la profilatura delle statistiche di esecuzione delle query v2

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

SQL Server 2016 (13.x) SP1 include una versione rivista della profilatura leggera con un overhead minimo. La profilatura leggera può anche essere abilitata a livello globale usando il flag di traccia 7412 per le versioni indicate in precedenza in Si applica a. Una nuova DMF sys.dm_exec_query_statistics_xml viene introdotta per restituire il piano di esecuzione della 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 leggera non è abilitata a livello globale, è possibile usare il nuovo argomento hint per la query QUERY_PLAN_PROFILE per abilitare la profilatura leggera a livello di query, per qualsiasi sessione. Al termine di una query contenente questo nuovo hint, viene restituito anche un nuovo query_plan_profile evento esteso che fornisce un xml del piano di esecuzione effettivo simile all'evento query_post_execution_showplan esteso.

Note

L'evento query_plan_profile esteso usa anche la profilatura leggera anche se l'hint per la query non viene usato.

Una sessione di esempio che usa l'evento query_plan_profile esteso può essere configurata come 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 leggera per la profilatura delle statistiche di esecuzione delle query v3

Si applica a: SQL Server 2019 (15.x) e versioni successive 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. In SQL Server 2019 (15.x) e versioni successive il flag di traccia 7412 non ha alcun effetto. La profilatura leggera può essere disabilitata a livello di database usando la LIGHTWEIGHT_QUERY_PROFILINGconfigurazione con ambito database: 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. L'ultima statistica del piano di query può essere abilitata a livello di database usando la LAST_QUERY_PLAN_STATSconfigurazione con ambito database: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Un nuovo query_post_execution_plan_profile evento esteso raccoglie l'equivalente di un piano di esecuzione effettivo basato sulla profilatura leggera, a differenza query_post_execution_showplandi , che usa la profilatura standard. SQL Server 2017 (14.x) offre anche questo evento a partire da CU14. Una sessione di esempio che usa l'evento query_post_execution_plan_profile esteso può essere configurata come 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 di 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 profilazione delle 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 più antica per cui è disponibile l'azione.

Scope Profilatura standard Profilatura leggera
Global Sessione evento estesa con XE query_post_execution_showplan ; A partire da SQL Server 2012 (11.x) Trace flag 7412; A partire da SQL Server 2016 (13.x) SP1
Global Traccia SQL e SQL Server Profiler con l'evento di Showplan XML traccia Sessione evento estesa con XE query_thread_profile ; A partire da SQL Server 2014 (12.x) SP2
Global N/A Sessione evento estesa con XE query_post_execution_plan_profile ; A partire da SQL Server 2017 (14.x) CU14 e SQL Server 2019 (15.x)
Session Utilizzare SET STATISTICS XML ON Usare l'hint per la QUERY_PLAN_PROFILE query insieme a una sessione di eventi estesi con XE query_plan_profile ; A partire da SQL Server 2016 (13.x) SP2 CU3 e SQL Server 2017 (14.x) CU11
Session Utilizzare SET STATISTICS PROFILE ON N/A
Session Selezionare il pulsante Statistiche query dinamiche in SSMS; A partire da SQL Server 2014 (12.x) SP2 N/A

Remarks

Important

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 leggera v2 e dal suo basso sovraccarico, qualsiasi server non già associato alla CPU può eseguire continuamente la profilatura leggera e consentire ai professionisti del database di accedere a qualsiasi esecuzione in esecuzione in qualsiasi momento, ad esempio usando Monitoraggio attività o eseguendo direttamente query e ottenere il piano di query sys.dm_exec_query_profilescon 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).

Gli eventi estesi che usano la profilatura leggera usano informazioni dalla profilatura standard, nel caso in cui l'infrastruttura di profilatura standard sia già abilitata. 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 usa comunque le informazioni della profilatura standard.

Note

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

Contenuti correlati

  • Last updated on