Infrastructuur voor queryprofilering

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric Preview

De SQL Server Database Engine biedt toegang 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. De toegang tot het daadwerkelijke uitvoeringsplan is dus 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 van het profiel voor queryuitvoeringsstatistieken of standaardprofilering moet zijn ingeschakeld voor het verzamelen van informatie over uitvoeringsplannen, namelijk aantal rijen, CPU en I/O-gebruik. De volgende methoden voor het verzamelen van informatie over het uitvoeringsplan voor een doelsessie maken gebruik van de standaardprofileringsinfrastructuur:

• STATISTIEKEN XML INSTELLEN
• STATISTIEKENPROFIEL INSTELLEN
• Live query-statistieken

De volgende methoden voor het verzamelen van informatie over het uitvoeringsplan voor alle sessies gebruiken de standaardprofileringsinfrastructuur:

• De query_post_execution_showplan uitgebreide gebeurtenis. Zie Systeemactiviteit bewaken met uitgebreide gebeurtenissen om 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, waarmee livequerystatistieken voor alle sessies worden ingeschakeld, met behulp van Activiteitsmonitor of rechtstreeks query's uitvoeren op de DMV. 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.

Lichtgewicht infrastructuur voor het profileren van query-uitvoeringsstatistieken v1

Van toepassing op: 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 CPU-runtimegegevens. 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 volgende 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
);

Bij het uitvoeren van een uitgebreide gebeurtenissessie die gebruikmaakt van de query_thread_profile gebeurtenis, wordt de sys.dm_exec_query_profiles DMV ook gevuld met lichtgewicht profilering, waardoor livequerystatistieken voor alle sessies mogelijk zijn, met behulp van Activiteitsmonitor of rechtstreeks query's uitvoeren op de DMV.

Lichtgewicht infrastructuur voor het profileren van statistieken van query-uitvoering v2

Van toepassing op: 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 traceringsvlag 7412 voor de eerder genoemde versies in Van toepassing zijn op. Er wordt een nieuwe DMF sys.dm_exec_query_statistics_xml geïntroduceerd om het queryuitvoeringsplan voor lopende aanvragen terug te geven.

Vanaf SQL Server 2016 (13.x) SP2 CU3 en SQL Server 2017 (14.x) CU11, als lichtgewicht profilering wereldwijd niet is ingeschakeld, kan het nieuwe argument QUERY_PLAN_PROFILE gebruiken 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 XML van een daadwerkelijk uitvoeringsplan biedt dat vergelijkbaar is met de query_post_execution_showplan uitgebreide gebeurtenis.

Een voorbeeldsessie met behulp van de query_plan_profile uitgebreide gebeurtenis kan worden geconfigureerd zoals in het volgende voorbeeld:

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 2019 (15.x) en latere versies 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. In SQL Server 2019 (15.x) en latere versies heeft traceringsvlag 7412 geen effect. Lichtgewicht profilering kan worden uitgeschakeld op databaseniveau met behulp van de configuratie van het LIGHTWEIGHT_QUERY_PROFILINGdatabasebereik: 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 worden ingeschakeld op databaseniveau met behulp van de configuratie van het LAST_QUERY_PLAN_STATSdatabasebereik: 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, dat 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 volgende 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.

Remarks

Beginnend met lichtgewicht profilering v2 en de lage overhead, kan elke server die nog niet cpu-afhankelijk is , doorlopend lichtgewicht profileren uitvoeren en databaseprofessionals toestaan om op elk gewenst moment gebruik te maken van actieve uitvoering, bijvoorbeeld door Activiteitsmonitor te gebruiken of rechtstreeks query's uit te voeren sys.dm_exec_query_profiles, en het queryplan op te halen met runtimestatistieken.

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

Uitgebreide gebeurtenissen die gebruikmaken van lichtgewicht profilering maken gebruik van informatie uit standaardprofilering, voor het geval 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 maakt nog steeds gebruik van informatie uit standaardprofilering.

Verwante inhoud

• Prestaties bewaken en afstemmen voor betere resultaten
• Hulpprogramma's voor het bewaken en afstemmen van prestaties
• Activiteitsmonitor openen in SQL Server Management Studio (SSMS)
• Activiteitsmonitor
• Prestaties controleren via de Query Store
• Systeemactiviteit bewaken met uitgebreide gebeurtenissen
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• Traceringsvlagmen instellen met DBCC TRACEON (Transact-SQL)
• Logische en fysieke referenties van showplan-operatoren
• werkelijke uitvoeringsplan
• Live query-statistieken