Infrastruttura per la profilazione delle query

Si applica a:SQL Serverdatabase SQL di AzureAzure 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 seguenti metodi per raccogliere informazioni sul piano di esecuzione per una sessione di destinazione utilizzano l'infrastruttura di profilatura standard:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Statistiche sulle query in tempo reale

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

• L'evento query_post_execution_showplan esteso. Per abilitare gli eventi estesi, vedere Monitorare l'attività di sistema tramite eventi estesi.
• L'evento di traccia Showplan XML in SQL Trace e SQL Server Profiler. Per altre informazioni sull'evento traccia, vedere Classe di evento Showplan XML.

Quando si esegue una sessione di eventi estesi che usa l'evento query_post_execution_showplan, viene popolata anche la DMV sys.dm_exec_query_profiles, che consente di visualizzare le statistiche delle query in tempo reale per tutte le sessioni, tramite Monitoraggio attività o interrogando direttamente la 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.

Infrastruttura leggerissima per la profilatura delle statistiche di esecuzione delle query 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 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 inoltre introdotto un nuovo evento esteso query_thread_profile che utilizza la profilazione 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
);

Quando si esegue una sessione di eventi estesi che usa l'evento query_thread_profile, anche la DMV sys.dm_exec_query_profiles viene popolata tramite la profilazione leggera, che consente di ottenere statistiche delle query in tempo reale per tutte le sessioni, tramite Monitoraggio attività o interrogando direttamente la DMV.

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

Si applica a: da 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 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 QUERY_PLAN_PROFILE del hint di query USE HINT per abilitare la profilatura leggera a livello di singola 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.

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 database SQL di Azure 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. Le statistiche dell'ultimo piano di query possono essere abilitate a livello di database tramite la LAST_QUERY_PLAN_STATSconfigurazione con ambito di 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 reale basato sulla 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. 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.

Remarks

A partire da lightweight profiling v2 e dal suo basso sovraccarico, qualsiasi server che non sia già limitato dalla CPU può eseguire la profilatura leggera continuamente e consentire ai professionisti del database di analizzare qualsiasi esecuzione in corso in qualsiasi momento, ad esempio usando Activity Monitor o interrogando direttamente sys.dm_exec_query_profiles, e ottenere il piano di esecuzione con 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 utilizzano la profilatura leggera utilizzano le informazioni della profilatura standard, qualora 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 continua a utilizzare le informazioni del profiling standard.

Contenuti correlati

• Monitoraggio e ottimizzazione delle prestazioni
• Strumenti di monitoraggio e ottimizzazione delle prestazioni
• Aprire Monitoraggio attività in SQL Server Management Studio (SSMS)
• Monitoraggio attività
• Monitorare le prestazioni tramite Query Store
• Monitorare l'attività del sistema mediante gli eventi estesi
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• Impostare i flag di traccia con DBCC TRACEON (Transact-SQL)
• Riferimento dell'operatore per piani di esecuzione logici e fisici
• piano di esecuzione effettivo
• Statistiche sulle query in tempo reale