Infrastruktura profilace dotazů

Článek
03/05/2025

platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance SQL Database v Microsoft Fabric

Databázový stroj SQL Serveru umožňuje přístup k informacím modulu runtime v plánech spouštění dotazů. Jednou z nejdůležitějších akcí, když dojde k problému s výkonem, je získat přesné porozumění úlohu, která se provádí, a jak je řízeno využití prostředků. Přístup k skutečnému plánu provádění je pro tento problém důležitý.

I když je dokončení dotazu předpokladem pro dostupnost skutečného plánu dotazu, statistiky živého dotazu mohou poskytnout přehledy o procesu provádění dotazu v reálném čase, když data plynou z jednoho operátoru plánu dotazu do jiného. Plán živého dotazu zobrazuje celkový průběh dotazu a statistiky spouštění na úrovni operátora, jako je počet řádků vytvořených, uplynulý čas, průběh operátoru atd. Vzhledem k tomu, že tato data jsou dostupná v reálném čase, aniž by bylo nutné čekat na dokončení dotazu, jsou tyto statistiky provádění velmi užitečné pro ladění problémů s výkonem dotazů, jako jsou dlouhotrvající dotazy a dotazy, které běží neomezeně dlouho a nikdy se nedokončí.

Standardní infrastruktura profilace statistik provádění dotazů

Aby bylo možné shromažďovat informace o plánech provádění, konkrétně o počtu řádků, využití procesoru a vstupně-výstupních operací, musí být povolená infrastruktura statistiky provádění dotazůnebo standardní profilace. Následující metody shromažďování informací o plánu provádění pro cílovou relaci využívají standardní infrastrukturu profilace:

Poznámka

Kliknutím na tlačítko Zahrnout statistiky živého dotazu v aplikaci SQL Server Management Studio se využívá standardní profilovací infrastruktura.
Pokud je ve vyšších verzích SQL Serveru povolena odlehčená infrastruktura profilování, jsou statistiky živého dotazu využívány místo standardního profilování při zobrazení prostřednictvím Monitoru aktivity nebo přímým dotazováním zobrazení dynamické správy sys.dm_exec_query_profiles.

Následující metody globálního shromažďování informací o prováděcím plánu pro všechny relace využívají standardní infrastrukturu profilování.

Rozšířená událost query_post_execution_showplan. Pokud chcete povolit rozšířené události, přečtěte si téma Monitorování systémové aktivity pomocí rozšířených událostí.
Událost trasování xml showplan v sql Trace a SQL Server Profiler. Další informace o této události trasování naleznete v tématu třída událostí Showplan XML.

Když spustíte rozšířenou relaci událostí, která používá událost query_post_execution_showplan, naplní se také sys.dm_exec_query_profiles DMV, což umožňuje statistiky živého dotazování pro všechny relace pomocí Prohlížeče aktivit nebo přímo dotazováním DMV. Další informace naleznete v části Live Query Statistics.

Zjednodušená infrastruktura profilace statistik provádění dotazů

Počínaje SQL Serverem 2014 (12.x) SP2 a SQL Serverem 2016 (13.x) byla zavedena nová lehká infrastruktura profilování statistik provádění dotazů, nebo lehká profilace.

Poznámka

Nativní zkompilované uložené procedury nejsou podporovány při lehké profilaci.

Lehká profilovací infrastruktura pro statistiky vykonávání dotazů v1

platí pro: SQL Server (SQL Server 2014 (12.x) SP2 až SQL Server 2016 (13.x)).

Počínaje verzí SQL Server 2014 (12.x) SP2 a SQL Server 2016 (13.x) se snížila výkonová režie pro sběr informací o plánech provádění v důsledku zavedení zjednodušené profilace. Na rozdíl od standardní profilace neshromažďuje zjednodušená profilace informace o modulu runtime procesoru. Zjednodušená profilace ale stále shromažďuje informace o počtu řádků a vstupně-výstupních operacích.

Byla zavedena také nová query_thread_profile rozšířená událost, která využívá odlehčené profilace. Tato rozšířená událost zveřejňuje statistiky provádění jednotlivých operátorů, což umožňuje lepší přehled o výkonu jednotlivých uzlů a vláken. Ukázkovou relaci, která používá tuto rozšířenou událost, je možné nakonfigurovat jako v následujícím příkladu:

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

Poznámka

Další informace o výkonnostním dopadu profilace dotazů najdete v blogovém příspěvku Volba vývojářů: Průběh dotazů – kdykoli, kdekoli.

Při spuštění rozšířené relace událostí, která používá událost query_thread_profile, se sys.dm_exec_query_profiles zobrazení dynamické správy naplní také pomocí zjednodušené profilace, která umožňuje statistiku živého dotazu pro všechny relace, pomocí sledování aktivit nebo přímo dotazování zobrazení dynamické správy.

Zjednodušená statistika provádění dotazů – profilace infrastruktury v2

platí pro: SQL Server (SQL Server 2016 (13.x) SP1 až SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1 obsahuje revidovanou verzi zjednodušeného profilování s minimální režií. Lehké profilování lze také povolit globálně pomocí trasovacího příznaku 7412 pro verze uvedené výše v sekci "Platí pro". Zavádí se nový DMF sys.dm_exec_query_statistics_xml, který vrátí plán provádění dotazů pro aktuálně zpracovávané požadavky.

Počínaje SQL Serverem 2016 (13.x) SP2 CU3 a SQL Serverem 2017 (14.x) CU11, pokud není povolená jednoduchá profilace globálně, můžete pro libovolnou relaci použít nový nápovědu k dotazu USE HINT argument QUERY_PLAN_PROFILE. Když se dokončí dotaz, který obsahuje tuto novou nápovědu, je také vytvořena nová rozšířená událost query_plan_profile, která poskytuje skutečný XML prováděcí plán podobný rozšířené události query_post_execution_showplan.

Poznámka

Rozšířená událost query_plan_profile také využívá odlehčené profilování, i když se nepoužije indikátor dotazu.

Ukázková relace pomocí rozšířené události query_plan_profile je možné nakonfigurovat jako v následujícím příkladu:

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

Zjednodušená statistika provádění dotazů profilace infrastruktury v3

platí pro: SQL Server (počínaje SQL Serverem 2019 (15.x)) a Azure SQL Database

SQL Server 2019 (15.x) a Azure SQL Database zahrnují nově upravenou verzi zjednodušené profilace, která shromažďuje informace o počtu řádků pro všechna spuštění. Zjednodušené profilování je ve výchozím nastavení povolené pro SQL Server 2019 (15.x) a Azure SQL Database. Počínaje SQL Serverem 2019 (15.x) nemá příznak trasování 7412 žádný účinek. Zjednodušené profilování lze zakázat na úrovni databáze pomocí databázové konfigurace LIGHTWEIGHT_QUERY_PROFILING : ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Zavádí se nový DMF sys.dm_exec_query_plan_stats, který vrátí ekvivalent posledního známého skutečného plánu provádění pro většinu dotazů a nazývá se statistiky posledního plánu dotazu. Statistiky plánu posledního dotazu lze povolit na úrovni databáze pomocí LAST_QUERY_PLAN_STATS konfigurace s vymezeným oborem databáze: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Nová rozšířená událost query_post_execution_plan_profile shromažďuje ekvivalent skutečného plánu provádění za použití odlehčené profilace, na rozdíl od query_post_execution_showplan, která využívá standardní profilaci. SQL Server 2017 (14.x) také nabízí tuto funkci počínaje verzí CU14. Ukázkovou relaci s rozšířenou událostí query_post_execution_plan_profile lze nakonfigurovat jako v následujícím příkladu:

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

Příklad 1 – Rozšířená relace událostí s využitím standardního profilování

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

Příklad 2 – Rozšířená relace událostí s využitím zjednodušené profilace

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

Pokyny k použití infrastruktury pro profilování dotazů

Následující tabulka shrnuje akce, které umožňují standardní profilaci nebo zjednodušené profilování, a to jak globálně (na úrovni serveru), tak i v jedné relaci. Obsahuje také nejstarší verzi, pro kterou je akce k dispozici.

Rozsah	Standardní profilace	Odlehčená profilace
Globální	Relace XEvent s `query_post_execution_showplan` XE; Počínaje SQL Serverem 2012 (11.x)	Příznak trasování 7412; počínaje SQL Serverem 2016 (verze 13.x) SP1.
Globální	Trasování SQL a SQL Server Profiler s událostí trasování `Showplan XML`; začínaje SQL Serverem 2000	Relace XEvent s `query_thread_profile` XE. Počínaje SQL Serverem 2014 (12.x) SP2
Globální	-	Relace XEvent s `query_post_execution_plan_profile` XE; Počínaje SQL Serverem 2017 (14.x) CU14 a SQL Serverem 2019 (15.x)
Sezení	Použití `SET STATISTICS XML ON`; Počínaje SQL Serverem 2000	Použijte nápovědu k dotazu `QUERY_PLAN_PROFILE` společně s relací XEvent s `query_plan_profile` XE; Počínaje SQL Serverem 2016 (13.x) SP2 CU3 a SQL Serverem 2017 (14.x) CU11
Sezení	Použití `SET STATISTICS PROFILE ON`; Počínaje SQL Serverem 2000	-
Sezení	V SSMS klikněte na tlačítko Statistiky živého dotazu. Počínaje SQL Serverem 2014 (verze 12.x) SP2	-

Poznámky

Důležitý

Vzhledem k možnému narušení náhodného přístupu při provádění uložené procedury monitorování, která odkazuje na sys.dm_exec_query_statistics_xml, ujistěte se, KB 4078596 je nainstalován v SQL Server 2016 (13.x) a SQL Server 2017 (14.x).

Počínaje zjednodušenou profilací v2 a nízkou režií může jakýkoli server, který ještě není vázán na procesor, spouštět odlehčené profilace nepřetržitěa umožnit odborníkům v databázích kdykoli klepnout na jakékoli spuštění, například pomocí monitorování aktivit nebo přímo dotazovat sys.dm_exec_query_profiles, a získat plán dotazu se statistikami modulu runtime.

Další informace o režijních nákladech na výkon profilace dotazů najdete v blogovém příspěvku Volba vývojářů: Průběh dotazů – kdykoli, kdekoli.

Poznámka

Rozšířené události, které využívají odlehčenou profilaci, budou používat informace ze standardního profilování v případě, že je už povolená standardní infrastruktura profilace. Například rozšířená relace událostí používající query_post_execution_showplan je spuštěná a spustí se jiná relace používající query_post_execution_plan_profile. Druhá relace bude nadále využívat informace ze standardní profilace.

Poznámka

Na SQL Serveru 2017 (14.x) je zjednodušené profilování ve výchozím nastavení vypnuté, ale aktivuje se při spuštění trasování XEvent, které spoléhá na query_post_execution_plan_profile, a po zastavení trasování se znovu deaktivuje. V důsledku toho, pokud jsou trasování XEvent založené na query_post_execution_plan_profile opakovaně spouštěné a zastavované v instanci SQL Serveru 2017 (14.x), důrazně doporučujeme aktivovat zjednodušené profilování na globální úrovni pomocí traceflag 7412, aby se zabránilo opakovanému zatížení aktivací a deaktivací.

Viz také

Monitorování a ladění výkonu
Nástroje pro monitorování a ladění výkonu
Otevřít správce aktivity (SQL Server Management Studio)
Monitorování aktivit
monitorování výkonu pomocí úložiště dotazů
Monitorování systémové aktivity pomocí rozšířených událostí
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
sledovací příznaky
Showplan: Odkaz na logické a fyzické operátory
skutečný plán provádění
Živé statistiky dotazu

Sdílet prostřednictvím

Infrastruktura profilace dotazů

Standardní infrastruktura profilace statistik provádění dotazů

Zjednodušená infrastruktura profilace statistik provádění dotazů

Lehká profilovací infrastruktura pro statistiky vykonávání dotazů v1

Zjednodušená statistika provádění dotazů – profilace infrastruktury v2

Zjednodušená statistika provádění dotazů profilace infrastruktury v3

Příklad 1 – Rozšířená relace událostí s využitím standardního profilování

Příklad 2 – Rozšířená relace událostí s využitím zjednodušené profilace

Pokyny k použití infrastruktury pro profilování dotazů

Poznámky

Viz také

Váš názor

Další materiály