Infrastructuur voor queryprofilering

Artikel
01/15/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-database in Microsoft Fabric

De SQL Server Database Engine biedt de mogelijkheid om toegang te krijgen tot runtime-informatie over queryuitvoeringsplannen. Een van de belangrijkste acties wanneer een prestatieprobleem optreedt, is om nauwkeurig inzicht te krijgen in de workload die wordt uitgevoerd en hoe resourcegebruik wordt aangestuurd. Hiervoor is toegang tot het werkelijke uitvoeringsplan belangrijk.

Hoewel het voltooien van query's een vereiste is voor de beschikbaarheid van een echt queryplan, kunnen live querystatistieken inzicht geven in het realtime queryuitvoeringsproces wanneer de gegevens stromen van de ene queryplanoperator naar een andere. In het livequeryplan worden de algemene uitvoeringsstatistieken van query's en uitvoeringsstatistieken op operatorniveau weergegeven, zoals het aantal geproduceerde rijen, verstreken tijd, de voortgang van de operator, enzovoort. Omdat deze gegevens in realtime beschikbaar zijn zonder te hoeven wachten tot de query is voltooid, zijn deze uitvoeringsstatistieken zeer nuttig voor het opsporen van problemen met queryprestaties, zoals langlopende query's en query's die voor onbepaalde tijd worden uitgevoerd en nooit worden voltooid.

De standaardinfrastructuur voor het profileren van queryuitvoeringsstatistieken

De profielinfrastructuur voor queryuitvoeringsstatistieken, of standaardprofilering, moet zijn ingeschakeld om informatie te verzamelen over uitvoeringsplannen, namelijk aantal rijen, CPU- en I/O-gebruik. De volgende methoden voor het verzamelen van informatie over het uitvoeringsplan voor een doelsessie gebruikmaken van de standaardprofileringsinfrastructuur:

Notitie

Wanneer u op de knop Live Query-statistieken opnemen klikt in SQL Server Management Studio, maakt u gebruik van de standaardprofileringsinfrastructuur.
In hogere versies van SQL Server, als de lichtgewicht profileringsinfrastructuur is ingeschakeld, wordt deze gebruikt door live query statistieken in plaats van standaardprofilering wanneer deze wordt bekeken via Activity Monitor of door rechtstreeks een query uit te voeren op de sys.dm_exec_query_profiles DMV.

De volgende methoden voor het wereldwijd verzamelen van informatie over het uitvoeringsplan voor alle sessies maken gebruik van de standaardprofileringsinfrastructuur:

De query_post_execution_showplan uitgebreide gebeurtenis. Zie Systeemactiviteit bewaken met uitgebreide gebeurtenissenom uitgebreide gebeurtenissen in te schakelen.
De Showplan XML-traceergebeurtenis in SQL Trace en SQL Server Profiler. Zie Showplan XML Event Classvoor meer informatie over deze traceringsevenement.

Bij het uitvoeren van een uitgebreide gebeurtenissessie die gebruikmaakt van de query_post_execution_showplan gebeurtenis, wordt de sys.dm_exec_query_profiles DMV ook ingevuld, waardoor live-querystatistieken voor alle sessies mogelijk zijn, met behulp van Activity Monitor of door direct de DMV te queryen. Zie Live Query Statisticsvoor meer informatie.

De lichtgewicht profileringsinfrastructuur voor queryuitvoeringsstatistieken

Vanaf SQL Server 2014 (12.x) SP2 en SQL Server 2016 (13.x) is een nieuwe lichtgewicht infrastructuur voor de profilering van queryuitvoeringsstatistieken, oftewel lichtgewicht profilering, geïntroduceerd.

Notitie

Systeemeigen gecompileerde opgeslagen procedures worden niet ondersteund met lichtgewicht profilering.

Lichtgewicht infrastructuur voor het profileren van query-uitvoeringsstatistieken v1

van toepassing op: SQL Server (SQL Server 2014 (12.x) SP2 tot en met SQL Server 2016 (13.x)).

Vanaf SQL Server 2014 (12.x) SP2 en SQL Server 2016 (13.x) is de prestatieoverhead voor het verzamelen van informatie over uitvoeringsplannen verminderd met de introductie van lichtgewicht profilering. In tegenstelling tot standaardprofilering verzamelt lichtgewicht profilering geen INFORMATIE over CPU-runtime. Lichtgewicht profilering verzamelt echter nog steeds het aantal rijen en I/O-gebruiksgegevens.

Er is ook een nieuwe query_thread_profile uitgebreide gebeurtenis geïntroduceerd die gebruikmaakt van lichtgewicht profilering. Met deze uitgebreide gebeurtenis worden uitvoeringsstatistieken per operator weergegeven, zodat u meer inzicht hebt in de prestaties van elk knooppunt en elke thread. Een voorbeeldsessie met deze uitgebreide gebeurtenis kan worden geconfigureerd zoals in het onderstaande voorbeeld:

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

Notitie

Zie het blogbericht Ontwikkelaarskeuze: Queryvoortgang - altijd en overalvoor meer informatie over de prestatieoverhead van queryprofilering.

Wanneer u een uitgebreide gebeurtenissessie uitvoert die gebruikmaakt van de query_thread_profile gebeurtenis, wordt de sys.dm_exec_query_profiles DMV ook gevuld met lichtgewicht profilering, waarmee livequerystatistieken voor alle sessies mogelijk zijn, met behulp van Activity Monitor- of rechtstreeks een query uitvoeren op de DMV.

Lichtgewicht profileringsinfrastructuur voor uitvoeringsstatistieken van query's v2

van toepassing op: SQL Server (SQL Server 2016 (13.x) SP1 tot en met SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1 bevat een herziene versie van lichtgewicht profilering met minimale overhead. Lichtgewicht profilering kan ook globaal worden ingeschakeld met behulp van traceringsvlag 7412 voor de hierboven genoemde versies in Van toepassing op. Er wordt een nieuwe DMF-sys.dm_exec_query_statistics_xml geïntroduceerd om het queryuitvoeringsplan voor aanvragen in vlucht te retourneren.

Vanaf SQL Server 2016 (13.x) SP2 CU3 en SQL Server 2017 (14.x) CU11, als lichtgewicht profilering wereldwijd niet is ingeschakeld, kan de nieuwe query hint USE HINT met het argument QUERY_PLAN_PROFILE worden gebruikt om lichtgewicht profilering op queryniveau in te schakelen voor elke sessie. Wanneer een query met deze nieuwe hint is voltooid, wordt er ook een nieuwe query_plan_profile uitgebreide gebeurtenis uitgevoerd die een werkelijke XML-uitvoeringsplan biedt die vergelijkbaar is met de query_post_execution_showplan uitgebreide gebeurtenis.

Notitie

De query_plan_profile uitgebreide gebeurtenis maakt ook gebruik van lichtgewicht profilering, zelfs als de query hint niet wordt gebruikt.

Een voorbeeldsessie met behulp van de query_plan_profile uitgebreide gebeurtenis kan worden geconfigureerd, zoals in het voorbeeld hieronder.

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

Lichtgewicht infrastructuur voor het profileren van query-uitvoeringsstatistieken v3

van toepassing op: SQL Server (te beginnen met SQL Server 2019 (15.x)) en Azure SQL Database

SQL Server 2019 (15.x) en Azure SQL Database bevatten een onlangs herziene versie van lichtgewicht profilering die informatie over het aantal rijen verzamelt voor alle uitvoeringen. Lichtgewicht profilering is standaard ingeschakeld op SQL Server 2019 (15.x) en Azure SQL Database. Vanaf SQL Server 2019 (15.x) heeft traceringsvlag 7412 geen effect. Lichtgewicht profilering kan worden uitgeschakeld op databaseniveau met behulp van de configuratie met LIGHTWEIGHT_QUERY_PROFILING databasebereik: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Er wordt een nieuwe DMF-sys.dm_exec_query_plan_stats geïntroduceerd om het equivalent van het laatst bekende uitvoeringsplan voor de meeste query's te retourneren en wordt laatste queryplanstatistiekengenoemd. De laatste statistieken van het queryplan kunnen op het niveau van de database worden ingeschakeld met behulp van de databasespecifieke configuratie LAST_QUERY_PLAN_STATS : ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Een nieuwe query_post_execution_plan_profile uitgebreide gebeurtenis verzamelt het equivalent van een daadwerkelijk uitvoeringsplan op basis van lichtgewicht profilering, in tegenstelling tot query_post_execution_showplan die gebruikmaakt van standaardprofilering. SQL Server 2017 (14.x) biedt deze gebeurtenis ook vanaf CU14. Een voorbeeldsessie met behulp van de query_post_execution_plan_profile uitgebreide gebeurtenis kan worden geconfigureerd, zoals in het onderstaande voorbeeld:

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

Voorbeeld 1: Uitgebreide gebeurtenissessie met standaardprofilering

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

Voorbeeld 2: Uitgebreide gebeurtenissessie met lichtgewicht 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);

Gebruiksrichtlijnen voor infrastructuur voor queryprofilering

De volgende tabel bevat een overzicht van de acties voor het inschakelen van standaardprofilering of lichtgewicht profilering, zowel globaal (op serverniveau) als in één sessie. Bevat ook de vroegste versie waarvoor de actie beschikbaar is.

Draagwijdte	Standaardprofilering	Lichtgewicht profilering
Globaal	XEvent-sessie met de `query_post_execution_showplan` XE; Vanaf SQL Server 2012 (11.x)	Traceringsvlag 7412; Vanaf SQL Server 2016 (13.x) SP1
Globaal	SQL Trace en SQL Server Profiler met de `Showplan XML` traceringsgebeurtenis; Vanaf SQL Server 2000	XEvent-sessie met de `query_thread_profile` XE; Vanaf SQL Server 2014 (12.x) SP2
Globaal	-	XEvent-sessie met de `query_post_execution_plan_profile` XE; Vanaf SQL Server 2017 (14.x) CU14 en SQL Server 2019 (15.x)
Sessie	Gebruik `SET STATISTICS XML ON`; Vanaf SQL Server 2000	Gebruik de `QUERY_PLAN_PROFILE` queryhint samen met een XEvent-sessie met de `query_plan_profile` XE; Vanaf SQL Server 2016 (13.x) SP2 CU3 en SQL Server 2017 (14.x) CU11
Sessie	Gebruik `SET STATISTICS PROFILE ON`; Vanaf SQL Server 2000	-
Sessie	Klik op de knop Live Query Statistics in SSMS: Vanaf SQL Server 2014 (12.x) SP2	-

Opmerkingen

Belangrijk

Als gevolg van een mogelijke schending van willekeurige toegang tijdens het uitvoeren van een opgeslagen bewakingsprocedure die verwijst naar sys.dm_exec_query_statistics_xml, moet u ervoor zorgen dat KB-4078596 is geïnstalleerd in SQL Server 2016 (13.x) en SQL Server 2017 (14.x).

Beginnend met lichtgewicht profilering v2 en de lage overhead, kan elke server die nog niet CPU-gebonden is, continulichtgewicht profilering uitvoeren en databaseprofessionals toestaan om op elk gewenst moment in te grijpen in een lopende uitvoering, bijvoorbeeld door de Activiteitsmonitor te gebruiken of direct query's uit te voeren , en het queryplan met runtimestatistieken op te halen.

Zie het blogbericht Ontwikkelaarskeuze: Queryvoortgang - altijd en overalvoor meer informatie over de prestatieoverhead van queryprofilering.

Notitie

Uitgebreide gebeurtenissen die gebruikmaken van lichtgewicht profilering gebruiken informatie uit standaardprofilering als de standaardprofileringsinfrastructuur al is ingeschakeld. Een uitgebreide gebeurtenissessie met query_post_execution_showplan wordt bijvoorbeeld uitgevoerd en er wordt een andere sessie gestart met behulp van query_post_execution_plan_profile. De tweede sessie gebruikt nog steeds informatie uit standaardprofilering.

Notitie

In SQL Server 2017 (14.x) is Lightweight Profilering standaard uitgeschakeld, maar wordt geactiveerd wanneer een XEvent-tracering afhankelijk is van query_post_execution_plan_profile wordt gestart en vervolgens opnieuw wordt gedeactiveerd wanneer de tracering wordt gestopt. Als Xevent-traceringen op basis van query_post_execution_plan_profile daarom regelmatig worden gestart en gestopt op een EXEMPLAAR van SQL Server 2017 (14.x), wordt u ten zeer slotte aangeraden Lightweight Profilering op globaal niveau te activeren met traceflag 7412 om de overhead voor herhaalde activering/deactivering te voorkomen.

Zie ook

Prestaties bewaken en afstemmen voor betere resultaten
hulpprogramma's voor prestatiebewaking en -afstemming
Activiteitsmonitor openen (SQL Server Management Studio)
activiteitsmonitor
Prestaties Bewaken Met Behulp Van De Query Store
Systeemactiviteit bewaken met uitgebreide gebeurtenissen
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
traceervlaggen
Verwijzing naar logische en fysieke operatoren weergeven
werkelijke uitvoeringsplan
Live Query Statistieken

Delen via

Infrastructuur voor queryprofilering

De standaardinfrastructuur voor het profileren van queryuitvoeringsstatistieken

De lichtgewicht profileringsinfrastructuur voor queryuitvoeringsstatistieken

Lichtgewicht infrastructuur voor het profileren van query-uitvoeringsstatistieken v1

Lichtgewicht profileringsinfrastructuur voor uitvoeringsstatistieken van query's v2

Lichtgewicht infrastructuur voor het profileren van query-uitvoeringsstatistieken v3

Voorbeeld 1: Uitgebreide gebeurtenissessie met standaardprofilering

Voorbeeld 2: Uitgebreide gebeurtenissessie met lichtgewicht profilering

Gebruiksrichtlijnen voor infrastructuur voor queryprofilering

Opmerkingen

Zie ook

Feedback

Aanvullende resources