Infrastruktur för frågeprofilering

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Databasmotor för SQL Server ger åtkomst till körningsinformation om frågekörningsplaner. En av de viktigaste åtgärderna när ett prestandaproblem uppstår är att få exakt förståelse för den arbetsbelastning som körs och hur resursanvändningen drivs. Därför är åtkomst till den faktiska körningsplanen viktig.

Även om slutförande av frågor är en förutsättning för tillgången till en faktisk frågeplan kan livefrågestatistik ge insikter i realtid om frågekörningsprocessen när data flödar från en frågeplansoperator till en annan. Live-frågeplanen visar den övergripande körningsstatistiken för frågeförlopp och körning på operatornivå, till exempel antalet rader som genereras, förfluten tid, operatorförlopp osv. Eftersom dessa data är tillgängliga i realtid utan att behöva vänta tills frågan har slutförts är den här körningsstatistiken mycket användbar för felsökning av problem med frågeprestanda, till exempel tidskrävande frågor och frågor som körs på obestämd tid och aldrig slutförs.

Den standardinfrastruktur för frågeutförandestatistikprofilering

Profilinfrastrukturen för statistik över frågekörning, eller standardprofilering, måste vara aktiverad för att samla in information om körningsplaner, närmare bestämt antal rader samt CPU- och I/O-användning. Följande metoder för att samla in körningsplaninformation för en målsession använder standardprofileringsinfrastrukturen:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Statistik för Livefrågor

Följande metoder för att globalt samla in information om exekveringsplaner för alla sessioner använder standardprofileringsinfrastrukturen:

• den utökade query_post_execution_showplan-händelsen. Information om hur du aktiverar utökade händelser finns i Övervaka systemaktivitet med hjälp av utökade händelser.
• Showplan XML spårningshändelsen i SQL Trace och SQL Server Profiler. Mer information om den här spårningshändelsen finns i Showplan XML Event Class.

När du kör en utökad händelsesession som använder query_post_execution_showplan-händelsen, fylls också DMV:n sys.dm_exec_query_profiles i, vilket möjliggör livefrågestatistik för alla sessioner med hjälp av Aktivitetsövervakaren eller genom att fråga DMV:n direkt. Mer information finns i Live Query Statistics.

Den enkla infrastrukturen för frågekörningsstatistikprofilering

Från och med SQL Server 2014 (12.x) SP2 och SQL Server 2016 (13.x), introducerades en ny lättviktig statistikprofileringsinfrastruktur för frågekörningeller enkel profilering.

Förenklad profileringsinfrastruktur för frågekörningsstatistik v1

Gäller för: SQL Server 2014 (12.x) SP2 via SQL Server 2016 (13.x).

Från och med SQL Server 2014 (12.x) SP2 och SQL Server 2016 (13.x) minskades prestandakostnaderna för att samla in information om körningsplaner i och med införandet av enkel profilering. Till skillnad från standardprofilering samlar lättviktsprofilering inte in information om CPU-körningstid. Även lättviktig profilering samlar fortfarande in information om antal rader och I/O-användning.

En ny query_thread_profile utökad händelse introducerades också som använder enkel profilering. Den här utökade händelsen exponerar körningsstatistik per operatör så att du får mer information om prestanda för varje nod och tråd. En exempelsession med den här utökade händelsen kan konfigureras som i följande exempel:

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

När du kör en utökad händelsesession som använder händelsen query_thread_profile, fylls även DMV:n sys.dm_exec_query_profiles i med lättviktig profilering, vilket möjliggör livefrågestatistik för alla sessioner med Activity Monitor eller genom att fråga DMV:n direkt.

Förenklad profileringsinfrastruktur för frågekörningsstatistik v2

Gäller för: SQL Server 2016 (13.x) SP1 via SQL Server 2017 (14.x).

SQL Server 2016 (13.x) SP1 innehåller en reviderad version av lättviktsprofilering med minimala omkostnader. Enkel profilering kan också aktiveras globalt med spårningsflagga 7412 för de versioner som angavs tidigare under Applikationer. En ny DMF-sys.dm_exec_query_statistics_xml introduceras för att returnera frågekörningsplanen för begäranden under flygning.

Från och med SQL Server 2016 (13.x) SP2 CU3 och SQL Server 2017 (14.x) CU11 kan, om lättviktsprofilering inte är aktiverad globalt, det nya argumentet USE HINT-frågetipsQUERY_PLAN_PROFILE användas för att aktivera lättviktsprofilering på frågenivå för alla sessioner. När en fråga som innehåller det här nya tipset har slutförts matas även en ny query_plan_profile utökad händelse ut som ger en faktisk XML-körningsplan som liknar den query_post_execution_showplan utökade händelsen.

En exempelsession med den query_plan_profile utökade händelsen kan konfigureras som i följande exempel:

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

Förenklad profileringsinfrastruktur för frågekörningsstatistik v3

gäller för: SQL Server 2019 (15.x) och senare versioner och Azure SQL Database

SQL Server 2019 (15.x) och Azure SQL Database innehåller en nyligen uppdaterad version av enkel profilering som samlar in information om antal rader för alla körningar. Enkel profilering är aktiverat som standard på SQL Server 2019 (15.x) och Azure SQL Database. I SQL Server 2019 (15.x) och senare versioner har spårningsflagga 7412 ingen effekt. Enkel profilering kan inaktiveras på databasnivå med hjälp av LIGHTWEIGHT_QUERY_PROFILINGdatabasens begränsade konfiguration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

En ny DMF sys.dm_exec_query_plan_stats introduceras för att returnera motsvarigheten till den senaste kända faktiska körningsplanen för de flesta frågor, och den kallas senaste frågeplansstatistiken. Den senaste frågeplansstatistiken kan aktiveras på databasnivå med hjälp av konfigurationen LAST_QUERY_PLAN_STATS med databasomfattning: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

En ny query_post_execution_plan_profile utökad händelse samlar in motsvarigheten till en faktisk körningsplan baserat på enkel profilering, till skillnad från query_post_execution_showplan, som använder standardprofilering. SQL Server 2017 (14.x) erbjuder även den här händelsen från och med CU14. En exempelsession med den query_post_execution_plan_profile utökade händelsen kan konfigureras som i följande exempel:

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

Exempel 1 – Utökad händelsesession med standardprofilering

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

Exempel 2 – Utökad händelsesession med lätt profilering

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

Användningsvägledning för frågeprofileringsinfrastruktur

I följande tabell sammanfattas åtgärderna för att aktivera standardprofilering eller enkel profilering, både globalt (på servernivå) eller i en enda session. Innehåller även den tidigaste versionen som åtgärden är tillgänglig för.

Remarks

Med lättviktsprofilering v2 och dess låga overhead kan alla servrar som inte redan är CPU-begränsade köra lättviktsprofilering kontinuerligt och ge databasspecialister möjlighet att få åtkomst till alla pågående körningar när som helst, till exempel genom att använda Aktivitetsövervakaren eller genom att köra frågor direkt mot sys.dm_exec_query_profiles, och hämta frågeplanen med körningsstatistik.

Mer information om prestandapåverkan vid frågeprofilering finns i blogginlägget ”Developers Choice: Query progress – anytime, anywhere”.

Utökade händelser som använder enkel profilering använder information från standardprofilering, om standardprofileringsinfrastrukturen redan är aktiverad. Till exempel körs en utökad händelsesession med query_post_execution_showplan och en annan session med query_post_execution_plan_profile startas. Den andra sessionen använder fortfarande information från standardprofilering.

Relaterat innehåll

• Övervaka och finjustera prestanda
• Verktyg för prestandaövervakning och justering
• Öppna Aktivitetsövervakaren i SQL Server Management Studio (SSMS)
• Aktivitetsövervakare
• Övervaka prestanda med hjälp av Query Store
• Övervaka systemaktivitet med hjälp av utökade händelser
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• Ange spårningsflaggor med DBCC TRACEON (Transact-SQL)
• Logisk och fysisk showplans-operator-referens
• faktiska exekveringsplanen
• Statistik för Livefrågor