Infrastruktur för frågeprofilering

Artikel
01/15/2025

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-databas i Microsoft Fabric

SQL Server Database Engine ger möjlighet till att komma åt information om exekveringstid för 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. För detta ä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.

Standardinfrastrukturen för frågekörningsstatistikprofilering

Frågekörningsstatistikens infrastruktur, eller standardprofil, måste aktiveras för att samla in information om körningsplaner, nämligen radantal, CPU-användning och I/O-användning. Följande metoder för att samla in körningsplansinformation för en målsession utnyttjar den standardiserade profileringsinfrastrukturen:

Anteckning

Om du klickar på knappen Ta med livefrågestatistik i SQL Server Management Studio används standardprofileringsinfrastrukturen.
Om den lättviktsprofileringsinfrastrukturen är aktiverad i senare versioner av SQL Server, används den av livedatafrågestatistik i stället för standardprofilering när den visas via Activity Monitor eller när man frågar DMV:n sys.dm_exec_query_profiles direkt.

Följande metoder för att samla in information om utförandeplan globalt för alla sessioner utnyttjar standardprofileringsinfrastrukturen:

Den query_post_execution_showplan utökade 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 händelsen query_post_execution_showplan, fylls också sys.dm_exec_query_profiles DMV i, vilket möjliggör direkt frågestatistik för alla sessioner, genom att använda Aktivitetsövervakaren eller genom att direkt köra frågor mot DMV:en. 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.

Not

Native-kompilerade lagrade procedurer stöds inte med lättviktsprofilering.

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

gäller för: SQL Server (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 enkel profilering inte in cpu-körningsinformation. Enkel profilering samlar dock fortfarande in information om antal rader och I/O-användning.

En ny query_thread_profile utökad händelse introducerades också som utnyttjar lättviktsprofilering. 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 exemplet nedan:

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

Obs

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

När du kör en utökad händelsesession som använder händelsen query_thread_profile fylls sys.dm_exec_query_profiles DMV också i med hjälp av enkel profilering, vilket möjliggör live-frågestatistik för alla sessioner, genom att använda Aktivitetsövervakare eller direktfråga DMV.

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

gäller för: SQL Server (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. En lätt profilering kan också aktiveras globalt med hjälp av spårningsflagga 7412 för de versioner som anges ovan i och gäller för. 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, om enkel profilering inte är aktiverad globalt, kan den nya USE HINT-frågetips argument QUERY_PLAN_PROFILE användas för att aktivera enkel profilering på frågenivå, för alla sessioner. När en fråga som innehåller det här nya tipset har slutförts, genereras utdata för en ny query_plan_profile utökad händelse som ger en faktisk körningsplanen i XML-formatet som liknar den utökade händelsen query_post_execution_showplan.

Anmärkning

Den query_plan_profile utökade händelsen utnyttjar också enkel profilering även om frågetipset inte används.

En exempelsession med hjälp av den query_plan_profile utökade händelsen kan konfigureras som exemplet nedan:

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 (från och med SQL Server 2019 (15.x)) 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. Från och med SQL Server 2019 (15.x) har spårningsflagga 7412 ingen effekt. Enkel profilering kan inaktiveras på databasnivå med hjälp av konfigurationen LIGHTWEIGHT_QUERY_PROFILING databasomfattning: 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 verkliga körningsplanen för de flesta frågor. Den kallas senaste frågeplansstatistiken. Den senaste frågeplansstatistiken kan aktiveras på databasnivå med hjälp av konfigurationen LAST_QUERY_PLAN_STATS databasomfattning: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Det finns nu ett nytt query_post_execution_plan_profile förlängt event som samlar in motsvarigheten till en faktisk körningsplan baserat på lättviktig 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 utökade händelsen query_post_execution_plan_profile kan konfigureras enligt exemplet nedan.

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.

Omfattning	Standardprofilering	Lätt profilering
Global	XEvent-session med `query_post_execution_showplan` XE; Börjar med SQL Server 2012 (11.x)	Spårningsflagga 7412; Från och med SQL Server 2016 (13.x) SP1
Global	SQL Trace och SQL Server Profiler med `Showplan XML` spårningshändelse; Börjar med SQL Server 2000	XEvent-session med `query_thread_profile` XE; Från och med SQL Server 2014 (12.x) SP2
Global	-	XEvent-session med `query_post_execution_plan_profile` XE; Från och med SQL Server 2017 (14.x) CU14 och SQL Server 2019 (15.x)
Session	Använd `SET STATISTICS XML ON`; Börjar med SQL Server 2000	Använd `QUERY_PLAN_PROFILE`-frågetipset tillsammans med en XEvent-session med `query_plan_profile` XE; Från och med SQL Server 2016 (13.x) SP2 CU3 och SQL Server 2017 (14.x) CU11
Session	Använd `SET STATISTICS PROFILE ON`; Börjar med SQL Server 2000	-
Session	Klicka på knappen Live Query Statistics i SSMS. Från och med SQL Server 2014 (12.x) SP2	-

Anmärkningar

Viktig

På grund av en eventuell slumpmässig åtkomstöverträdelse när du kör en övervakad lagrad procedur som refererar till sys.dm_exec_query_statistics_xmlkontrollerar du att KB-4078596 är installerad i SQL Server 2016 (13.x) och SQL Server 2017 (14.x).

Från och med lättviktig profilering v2 och dess låga omkostnader kan alla servrar som inte redan är CPU-bundna köra lättviktig profilering kontinuerligtoch tillåta databaspersonal att utnyttja exekvering när som helst, till exempel med Aktivitetsövervakaren eller direkt genom att göra förfrågningar sys.dm_exec_query_profilesoch hämta frågeplanen tillsammans med körningsstatistik.

Mer information om prestandakostnaderna för frågeprofilering finns i blogginlägget Developers Choice: Query progress – anytime, anywhere.

Anteckning

Utökade händelser som utnyttjar 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.

Not

På SQL Server 2017 (14.x) är Lightweight Profiling av som standard men aktiveras när en XEvent-spårning som förlitar sig på query_post_execution_plan_profile startas och sedan inaktiveras igen när spårningen stoppas. Om Xevent-spårningar baserade på query_post_execution_plan_profile startas ofta och stoppas på en SQL Server 2017-instans (14.x) rekommenderar vi därför starkt att du aktiverar Lightweight Profiling på global nivå med traceflag 7412 för att undvika upprepad aktivering/inaktivering.

Se även

Övervaka och finjustera prestanda
verktyg för prestandaövervakning och justering
Öppna Aktivitetsövervakning (SQL Server Management Studio)
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
Spårningsflaggor
Referens för logiska och fysiska operatorer för Showplan
faktiska exekveringsplanen
Statistik för Live-fråga

Dela via