Infrastruktura profilowania zapytań

Artykuł
03/05/2025

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Baza danych SQL w usłudze Microsoft Fabric

Silnik bazy danych programu SQL Server umożliwia dostęp do informacji o środowisku uruchomieniowym dotyczących planów wykonywania zapytań. Jedną z najważniejszych akcji występujących w przypadku wystąpienia problemu z wydajnością jest dokładne zrozumienie obciążenia, które jest wykonywane i jak jest napędzane użycie zasobów. W tym celu dostęp do rzeczywistego planu wykonania jest ważny.

Chociaż finalizacja zapytania jest wymaganiem wstępnym dla dostępności rzeczywistego planu zapytania, statystyki zapytań na żywo mogą zapewnić wgląd na bieżąco w proces realizacji zapytania w miarę jak dane przepływają z jednego operatora planu zapytania do innego. Plan zapytania na żywo wyświetla ogólny postęp zapytania oraz statystyki wykonania na poziomie operatora, takie jak liczba utworzonych wierszy, upłyniony czas, postęp operatora itp. Ponieważ te dane są dostępne w czasie rzeczywistym bez konieczności oczekiwania na zakończenie zapytania, te statystyki wykonania są niezwykle przydatne do debugowania problemów z wydajnością zapytań, takich jak długotrwałe zapytania lub zapytania działające w nieskończoność.

Standardowa infrastruktura profilowania statystyk wykonywania zapytań

Infrastruktura profilu statystyk wykonywania zapytań , czyli standardowe profilowanie, musi być włączone, aby zbierać informacje o planach wykonywania, a mianowicie o liczbie wierszy, użyciu CPU i operacji we/wy. Następujące metody zbierania informacji o planie wykonywania dla sesji docelowej korzystają ze standardowej infrastruktury profilowania:

Notatka

Kliknięcie przycisku Uwzględnij Statystyki Zapytania w Czasie Rzeczywistym w programie SQL Server Management Studio korzysta ze standardowej infrastruktury profilowania.
W wyższych wersjach programu SQL Server, jeśli włączono uproszczonej infrastruktury profilowania, jest ona używana przez statystyki zapytań na żywo zamiast standardowego profilowania podczas przeglądania za pośrednictwem monitora aktywności lub bezpośredniego wykonywania zapytań dotyczących sys.dm_exec_query_profiles widoku DMV.

Następujące metody zbierania informacji o planie wykonywania globalnie dla wszystkich sesji korzystają ze standardowej infrastruktury profilowania:

Zdarzenie rozszerzone query_post_execution_showplan. Aby włączyć zdarzenia rozszerzone, zobacz Monitorowanie aktywności systemu przy użyciu zdarzeń rozszerzonych.
Zdarzenie śledzenia XML Showplan w SQL Trace i SQL Server Profiler. Aby uzyskać więcej informacji na temat tego zdarzenia śledzenia, zobacz Klasa zdarzeń XML programu Showplan.

Podczas uruchamiania rozszerzonej sesji zdarzeń, która używa zdarzenia query_post_execution_showplan, widok zarządzania sys.dm_exec_query_profiles jest również wypełniany, co umożliwia uzyskanie statystyk zapytań na żywo dla wszystkich sesji, przy użyciu Monitor aktywności lub bezpośrednio odpytywać widok DMV. Aby uzyskać więcej informacji, sprawdź Statystyki zapytań na żywo.

Uproszczona infrastruktura profilowania statystyk wykonywania zapytań

Począwszy od programu SQL Server 2014 (12.x) SP2 i SQL Server 2016 (13.x), wprowadzono nową uproszczonej infrastruktury profilowania statystyk wykonywania zapytańlub uproszczone profilowanie.

Uwaga

Natywnie skompilowane procedury składowane nie są obsługiwane w przypadku uproszczonego profilowania.

Uproszczona infrastruktura profilowania statystyk wykonywania zapytań w wersji 1

Dotyczy: SQL Server (SQL Server 2014 (12.x) SP2 do SQL Server 2016 (13.x)).

Począwszy od programu SQL Server 2014 (12.x) SP2 i PROGRAMU SQL Server 2016 (13.x), obciążenie związane z wydajnością zbierania informacji o planach wykonywania zostało zmniejszone wraz z wprowadzeniem uproszczonego profilowania. W przeciwieństwie do profilowania standardowego profilowanie uproszczone nie zbiera informacji o środowisku uruchomieniowym procesora CPU. Jednak uproszczone profilowanie nadal zbiera informacje o liczbie wierszy i użyciu operacji we/wy.

Wprowadzono również nowe rozszerzone zdarzenie query_thread_profile, wykorzystujące uproszczone profilowanie. To zdarzenie rozszerzone uwidacznia statystyki wykonywania poszczególnych operatorów, co pozwala uzyskać lepszy wgląd w wydajność każdego węzła i wątku. Przykładowa sesja korzystająca z tego zdarzenia rozszerzonego można skonfigurować tak jak w poniższym przykładzie:

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

Notatka

Aby uzyskać więcej informacji na temat obciążenia związanego z wydajnością profilowania zapytań, zobacz wpis w blogu Developers Choice: Query progress - anytime, anywhere.

Podczas uruchamiania rozszerzonej sesji zdarzeń, która używa zdarzenia query_thread_profile, dynamiczny widok zarządzania sys.dm_exec_query_profiles jest również wypełniany przy użyciu uproszczonego profilowania. To umożliwia dostęp do statystyk zapytań w czasie rzeczywistym dla wszystkich sesji za pomocą Monitora Aktywności lub bezpośrednich zapytań dotyczących dynamicznego widoku zarządzania.

Lekka infrastruktura profilowania statystyk wykonywania zapytań wersja 2

Dotyczy: SQL Server (SQL Server 2016 (13.x) SP1 do SQL Server 2017 (14.x)).

Program SQL Server 2016 (13.x) SP1 zawiera poprawioną wersję uproszczonego profilowania z minimalnym obciążeniem. Uproszczone profilowanie można również włączyć globalnie przy użyciu flagi śledzenia 7412 dla wersji wymienionych powyżej w sekcji Dotyczy. Wprowadzono nowy sys.dm_exec_query_statistics_xml DMF w celu zwrócenia planu wykonywania zapytań dla żądań w locie.

Począwszy od aplikacji SQL Server 2016 (13.x) z wersją SP2 CU3 i SQL Server 2017 (14.x) CU11, jeśli uproszczone profilowanie nie jest włączone globalnie, wówczas nowa wskazówka zapytania USE HINT argument QUERY_PLAN_PROFILE może być użyty do włączenia uproszczonego profilowania na poziomie zapytania dla każdej sesji. Po zakończeniu zapytania zawierającego tę nową wskazówkę nowe zdarzenie rozszerzone query_plan_profile jest również danymi wyjściowymi zawierającymi rzeczywisty kod XML planu wykonania podobny do zdarzenia rozszerzonego query_post_execution_showplan.

Notatka

Zdarzenie rozszerzone query_plan_profile korzysta również z uproszczonego profilowania, nawet jeśli wskazówka zapytania nie jest używana.

Przykładowa sesja używająca zdarzenia rozszerzonego query_plan_profile można skonfigurować tak jak w poniższym przykładzie:

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

Uproszczone statystyki wykonywania zapytań profilowania infrastruktury w wersji 3

Dotyczy: SQL Server (począwszy od programu SQL Server 2019 (15.x)) i usługi Azure SQL Database

Program SQL Server 2019 (15.x) i usługa Azure SQL Database zawierają nowo zmienioną wersję uproszczonego profilowania zbierającego informacje o liczbie wierszy dla wszystkich wykonań. Uproszczone profilowanie jest domyślnie włączone w programie SQL Server 2019 (15.x) i usłudze Azure SQL Database. Począwszy od programu SQL Server 2019 (15.x), flaga śledzenia 7412 nie ma żadnego wpływu. Uproszczone profilowanie można wyłączyć na poziomie bazy danych przy użyciu konfiguracji o zakresie LIGHTWEIGHT_QUERY_PROFILING bazy danych: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Wprowadzono nową funkcję DMF sys.dm_exec_query_plan_stats, aby zwrócić odpowiednik ostatniego znanego rzeczywistego planu wykonania dla większości zapytań, i jest ona nazywana statystykami ostatniego planu zapytań . Statystyki planu ostatniego zapytania można włączyć na poziomie bazy danych, używając konfiguracji ukierunkowanej na bazę danych LAST_QUERY_PLAN_STATS : ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Nowe rozszerzone zdarzenie query_post_execution_plan_profile zbiera rzeczywisty plan wykonania na podstawie lekkiego profilowania, w przeciwieństwie do query_post_execution_showplan, które korzysta ze standardowego profilowania. Program SQL Server 2017 (14.x) oferuje również to zdarzenie, od wersji CU14. Przykładowa sesja używająca zdarzenia rozszerzonego query_post_execution_plan_profile można skonfigurować tak jak w poniższym przykładzie:

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

Przykład 1 — rozszerzona sesja zdarzeń przy użyciu profilowania standardowego

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

Przykład 2 — rozszerzona sesja zdarzeń przy użyciu uproszczonego profilowania

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

Wskazówki dotyczące użycia infrastruktury profilowania zapytań

Poniższa tabela zawiera podsumowanie akcji umożliwiających profilowanie standardowe lub uproszczone profilowanie, zarówno globalnie (na poziomie serwera) jak i w jednej sesji. Zawiera również najwcześniejszą wersję, dla której jest dostępna akcja.

Zakres	Profilowanie standardowe	Lekka profilowanie
Globalny	Sesja XEvent z `query_post_execution_showplan` XE; Począwszy od programu SQL Server 2012 (11.x)	Flaga śledzenia 7412; Począwszy od programu SQL Server 2016 (13.x) SP1
Globalny	Śledzenie SQL i Profiler SQL Server przy użyciu zdarzenia śledzenia `Showplan XML`, począwszy od wersji SQL Server 2000.	Sesja XEvent zidentyfikowana jako `query_thread_profile` XE; Począwszy od SQL Server 2014 (12.x) SP2
Globalny	-	Sesja XEvent z `query_post_execution_plan_profile` XE; Począwszy od SQL Server 2017 (14.x) CU14 i SQL Server 2019 (15.x)
Sesja	Użyj `SET STATISTICS XML ON`; Począwszy od programu SQL Server 2000	Użyj wskazówki zapytania `QUERY_PLAN_PROFILE` razem z sesją XEvent z `query_plan_profile` XE; Zaczynając od SQL Server 2016 (13.x) SP2 CU3 i SQL Server 2017 (14.x) CU11
Sesja	Użyj `SET STATISTICS PROFILE ON`; Począwszy od programu SQL Server 2000	-
Sesja	Kliknij przycisk Statystyki zapytań na żywo w programie SSMS; Począwszy od programu SQL Server 2014 (12.x) SP2	-

Uwagi

Ważny

Ze względu na możliwe naruszenie zabezpieczeń dostępu podczas wykonywania monitorującej procedury składowanej, która odwołuje się do sys.dm_exec_query_statistics_xml, upewnij się, że KB 4078596 jest zainstalowany w SQL Server 2016 (13.x) i SQL Server 2017 (14.x).

Począwszy od uproszczonego profilowania w wersji 2 i niskiego obciążenia, każdy serwer, który nie jest jeszcze powiązany z procesorem CPU, może uruchamiać uproszczone profilowanie stalei umożliwiać specjalistom bazy danych korzystanie z dowolnego uruchomionego wykonywania w dowolnym momencie, na przykład przy użyciu monitora aktywności lub bezpośredniego wykonywania zapytań sys.dm_exec_query_profilesi uzyskać plan zapytania ze statystykami środowiska uruchomieniowego.

Aby uzyskać więcej informacji na temat obciążenia wydajnościowego profilowania zapytań, zobacz post na blogu Developers Choice: Query progress - anytime, anywhere.

Notatka

Zdarzenia rozszerzone korzystające z uproszczonego profilowania będą używać informacji ze standardowego profilowania w przypadku, gdy standardowa infrastruktura profilowania jest już włączona. Na przykład jest uruchomiona rozszerzona sesja zdarzeń używająca query_post_execution_showplan, a uruchomiona jest kolejna sesja używająca query_post_execution_plan_profile. Druga sesja będzie nadal wykorzystywać dane ze standardowego profilowania.

Notatka

W programie SQL Server 2017 (14.x) profilowanie uproszczone jest domyślnie wyłączone, ale jest aktywowane po uruchomieniu śledzenia XEvent na query_post_execution_plan_profile, a następnie jest ponownie dezaktywowane po zatrzymaniu śledzenia. W związku z tym, jeśli ślady Xevent oparte na query_post_execution_plan_profile są często uruchamiane i zatrzymywane w wystąpieniu programu SQL Server 2017 (14.x), zdecydowanie zaleca się aktywowanie uproszczonego profilowania na poziomie globalnym przy użyciu traceflag 7412, aby uniknąć powtarzającego się obciążenia aktywacji/dezaktywacji.

Zobacz też

Monitorowanie i dostrajanie pod kątem wydajności
narzędzia do monitorowania wydajności i dostrajania
Otwórz Monitor Aktywności (SQL Server Management Studio)
Monitor aktywności
Monitorowanie wydajności przy użyciu magazynu zapytań
Monitorowanie aktywności systemowej przy użyciu zdarzeń rozszerzonych
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
flagi śledzenia
Odwołanie do operatorów logicznych i fizycznych Showplan
rzeczywisty plan wykonania
statystyki zapytań na żywo

Udostępnij za pośrednictwem

Infrastruktura profilowania zapytań

Standardowa infrastruktura profilowania statystyk wykonywania zapytań

Uproszczona infrastruktura profilowania statystyk wykonywania zapytań

Uproszczona infrastruktura profilowania statystyk wykonywania zapytań w wersji 1

Lekka infrastruktura profilowania statystyk wykonywania zapytań wersja 2

Uproszczone statystyki wykonywania zapytań profilowania infrastruktury w wersji 3

Przykład 1 — rozszerzona sesja zdarzeń przy użyciu profilowania standardowego

Przykład 2 — rozszerzona sesja zdarzeń przy użyciu uproszczonego profilowania

Wskazówki dotyczące użycia infrastruktury profilowania zapytań

Uwagi

Zobacz też

Opinia

Dodatkowe zasoby