Profilerstellungsinfrastruktur für Abfragen

Artikel
08/23/2023

Gilt für: SQL Server Azure SQL-Datenbank

Die SQL Server Datenbank-Engine bietet die Möglichkeit, auf Laufzeitinformationen für Abfrageausführungspläne zuzugreifen. Eine der wichtigsten Aktionen beim Auftreten eines Leistungsproblems besteht darin, ein genaues Verständnis der Workload zu erlangen, die ausgeführt wird, und zu ermitteln, wie die Ressourcenauslastung gesteuert wird. Für diese Erkenntnisse ist Zugriff auf den tatsächlichen Ausführungsplan wichtig.

Während der Abschluss der Abfrage eine Voraussetzung für die Verfügbarkeit eines aktuellen Abfrageplans ist, können Live-Abfragestatistiken Einblicke in Echtzeit in den Abfrageausführungsprozess gewähren, während die Daten von einem Abfrageplanoperator zu einem anderen fließen. Der Live-Abfrageplan zeigt den gesamten Abfragestatus und die Laufzeit-Ausführungsstatistik auf Operatorebene an, wie z.B. die Anzahl der erzeugten Zeilen, die verstrichene Zeit, den Operatorstatus usw. Da diese Daten in Echtzeit verfügbar sind und es nicht nötig ist, auf den Abschluss der Abfrage zu warten, sind diese Ausführungsstatistiken äußerst nützlich, um Probleme mit der Abfrageleistung zu beheben, z. B. lang laufende Abfragen und Abfragen, die unendlich lange laufen und nie beendet werden.

Standard-Profilerstellungsinfrastruktur für Abfrageausführungsstatistiken

Die Profilerstellungsinfrastruktur für Abfrageausführungsstatistiken (Standardprofilerstellung) muss aktiviert sein, um Informationen über Ausführungspläne zu erfassen, nämlich Zeilenanzahl, CPU- und E/A-Auslastung. Die folgenden Methoden zum Erfassen von Ausführungsplaninformationen für eine Zielsitzung nutzen die Standard-Profilerstellungsinfrastruktur:

Hinweis

Wenn Sie auf die Schaltfläche Live-Abfragestatistik einschließen in SQL Server Management Studio klicken, wird die Standard-Profilerstellungsinfrastruktur verwendet.
Wenn in höheren Versionen von SQL Server die Lightweight-Profiling-Infrastruktur aktiviert ist, wird sie durch Live-Abfragestatistiken anstelle des Standard-Profilings genutzt, wenn sie über den Aktivitätsmonitor oder durch direkte Abfrage der DMV sys.dm_exec_query_profiles angezeigt wird.

Die folgenden Methoden zum globalen Erfassen von Ausführungsplaninformationen für alle Sitzungen nutzen die Standard-Profilerstellungsinfrastruktur:

Das erweiterte Ereignis query_post_execution_showplan. Informationen zum Aktivieren von erweiterten Ereignissen finden Sie unter Überwachen der Systemaktivität mit erweiterten Ereignissen.
Das Ablaufverfolgungsereignis Showplan XML in der SQL-Ablaufverfolgung und in SQL Server Profiler. Weitere Informationen zu diesem Ablaufverfolgungsereignis finden Sie unter Showplan XML-Ereignisklasse.

Wenn Sie eine erweiterte Ereignissitzung ausführen, die das Ereignis query_post_execution_showplan verwendet, dann wird die sys.dm_exec_query_query_profiles-DMV ebenfalls mit Daten aufgefüllt. Dies ermöglicht Live-Abfragestatistiken für alle Sitzungen, indem der Aktivitätsmonitor verwendet oder die DMV direkt abgefragt wird. Weitere Informationen finden Sie unter Live Query Statistics.

Die einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik

Ab SQL Server 2014 (12.x) SP2 und SQL Server 2016 (13.x) wurde eine neue Profilerstellungsinfrastruktur für einfache Abfrageausführungsstatistiken oder einfache Profilerstellung eingeführt.

Hinweis

Nativ kompilierte gespeicherte Prozeduren werden bei der einfachen Profilerstellung nicht unterstützt.

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v1

Gilt für: SQL Server (SQL Server 2014 (12.x) SP2 durch SQL Server 2016 (13.x)).

Beginnend mit SQL Server 2014 (12.x) SP2 und SQL Server 2016 (13.x) wurde der Leistungsmehraufwand für die Erfassung von Informationen zu Ausführungsplänen durch die Einführung von einfacher Profilerstellung verringert. Im Gegensatz zur Standardprofilerstellung erfasst die einfache Profilerstellung keine CPU-Laufzeitinformationen. Allerdings erfasst die einfache Profilerstellung weiterhin die Zeilenanzahl und Informationen zur E/A-Verwendung.

Ein neues erweitertes Ereignis query_thread_profile wurde ebenfalls eingeführt, das einfache Profilerstellung nutzt. Dieses erweiterte Ereignis stellt Statistiken zur Abfrageausführung pro Operator bereit und ermöglicht einen besseren Einblick in die Leistung der einzelnen Knoten und Threads. Eine Beispielsitzung mit diesem erweiterten Ereignis kann wie im folgenden Beispiel konfiguriert werden:

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

Hinweis

Weitere Informationen zum Leistungsmehraufwand bei der Abfrageprofilerstellung finden Sie im Blogbeitrag Developers Choice: Query progress – anytime, anywhere.

Wenn Sie eine erweiterte Ereignissitzung ausführen, die das Ereignis query_thread_profile verwendet, dann wird die sys.dm_exec_query_query_profiles-DMV ebenfalls unter Verwendung von einfacher Profilerstellung mit Daten aufgefüllt. Dies ermöglicht Live-Abfragestatistiken für alle Sitzungen, indem der Aktivitätsmonitor verwendet oder die DMV direkt abgefragt wird.

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v2

Gilt für: SQL Server (SQL Server 2016 (13.x) SP1 durch SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1 enthält eine überarbeitete Version der einfachen Profilerstellung mit minimalem Mehraufwand. Einfache Profilerstellung kann auch global über das Ablaufverfolgungsflag 7412 für die Versionen aktiviert werden, die oben unter Gilt für angegeben werden. Eine neue DMF sys.dm_exec_query_statistics_xml wurde eingeführt, um den Abfrageausführungsplan für In-Flight-Anforderungen zurückzugeben.

Beginnend mit SQL Server 2016 (13.x) SP2 CU3 und SQL Server 2017 (14.x) CU11 gilt Folgendes: Wenn einfache Profilerstellung nicht global aktiviert ist, kann das Argument QUERY_PLAN_PROFILE des neuen USE HINT-Abfragehinweises verwendet werden, um einfache Profilerstellung auf Abfrageebene für jede beliebige Sitzung zu aktivieren. Wenn eine Abfrage, die diesen neuen Hinweis enthält, abgeschlossen wird, wird auch ein neues erweitertes Ereignis query_plan_profile ausgegeben, das XML für einen tatsächlichen Ausführungsplan ähnlich dem erweiterten Ereignis query_post_execution_showplan bereitstellt.

Hinweis

Das erweiterte Ereignis query_plan_profile nutzt zudem die einfache Profilerstellung, auch wenn der Abfragehinweis nicht verwendet wird.

Eine einfache Sitzung mit dem erweiterten Ereignis query_plan_profile kann wie im unten stehenden Beispiel konfiguriert werden:

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

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v3

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

SQL Server 2019 (15.x) und Azure SQL Database enthalten eine neue, überarbeitete Version von Lightweight Profiling, die Informationen zur Zeilenzahl für alle Ausführungen sammelt. Lightweight Profiling ist standardmäßig auf SQL Server 2019 (15.x) und Azure SQL Database aktiviert. Ab SQL Server 2019 (15.x) hat das Ablaufverfolgungsflag 7412 keine Wirkung mehr. Die Lightweight-Profilerstellung kann mithilfe der datenbankweit gültigen Konfiguration LIGHTWEIGHT_QUERY_PROFILING auf Datenbankebene deaktiviert werden: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Die neue dynamische Verwaltungsfunktion sys.dm_exec_query_plan_stats wird eingeführt, um das Äquivalent des letzten bekannten, tatsächlichen Ausführungsplans für die meisten Abfragen zurückzugeben. Diese heißt Abfrageplanstatistik. Die letzte Abfrageplanstatistik kann auf Datenbankebene mithilfe der datenbankweit gültigen Konfiguration LAST_QUERY_PLAN_STATS deaktiviert werden: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Im Gegensatz zum Ereignis query_post_execution_showplan, das die Standardprofilerstellung nutzt, erfasst das neue erweiterte Ereignis query_post_execution_plan_profile das Äquivalent eines tatsächlichen Ausführungsplans mithilfe einfacher Profilerstellung. SQL Server 2017 (14.x) bietet dieses Ereignis ab CU14 ebenfalls an. Sie können eine Beispielsitzung wie im folgenden Beispiel mithilfe des erweiterten Ereignisses query_post_execution_plan_profile konfigurieren:

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

Beispiel 1: Erweiterte Ereignissitzung mit der Standardprofilerstellung

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

Beispiel 2: Erweiterte Ereignissitzung mit der einfachen Profilerstellung

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

Leitfaden für die Verwendung der Profilerstellungsinfrastruktur für Abfragen

In der folgenden Tabelle werden die Aktionen zum Aktivieren der Standard- oder Lightweight-Profilerstellung zusammengefasst, sowohl global (auf Serverebene) als auch in einer einzelnen Sitzung. Schließt auch die niedrigste Version ein, für die die Aktion verfügbar ist.

`Scope`	Standardprofilerstellung	Lightweight-Profilerstellung
Global	XEvent-Sitzung mit dem `query_post_execution_showplan` XE; Ab SQL Server 2012 (11.x)	Ablaufverfolgungsflag 7412; Ab SQL Server 2016 (13.x) SP1
Global	SQL-Ablaufverfolgung und SQL Server Profiler mit Ablaufverfolgungsereignis `Showplan XML`, ab SQL Server 2000	XEvent-Sitzung mit dem `query_thread_profile` XE; Ab SQL Server 2014 (12.x) SP2
Global	-	XEvent-Sitzung mit dem `query_post_execution_plan_profile` XE; Ab SQL Server 2017 (14.x) CU14 und SQL Server 2019 (15.x)
Sitzung	Verwendung von `SET STATISTICS XML ON`, ab SQL Server 2000	Verwenden Sie den `QUERY_PLAN_PROFILE` Abfragehinweis zusammen mit einer XEvent-Sitzung mit dem `query_plan_profile` XE; Ab SQL Server 2016 (13.x) SP2 CU3 und SQL Server 2017 (14.x) CU11
Sitzung	Verwendung von `SET STATISTICS PROFILE ON`, ab SQL Server 2000	-
Sitzung	Klicken Sie auf die Schaltfläche Live-Abfragestatistik in SSMS; ab SQL Server 2014 (12.x) SP2	-

Hinweise

Wichtig

Stellen Sie aufgrund einer möglichen Verletzung des zufälligen Zugriffs beim Ausführen einer gespeicherten Überwachungsprozedur, die auf sys.dm_exec_query_statistics_xml verweist, sicher, dass KB-4078596 in SQL Server 2016 (13.x) und SQL Server 2017 (14.x) installiert ist.

Beginnend mit der einfachen Profilerstellung v2 und ihrem geringen Mehraufwand kann jeder Server, der nicht bereits CPU-gebunden ist, einfache Profilerstellung kontinuierlich ausführen und es Datenbankexperten ermöglichen, jederzeit auf jede aktuell ausgeführte Ausführung zuzugreifen (z.B. mit dem Aktivitätsmonitor oder durch direktes Abfragen von sys.dm_exec_query_profiles) und den Abfrageplan mit Laufzeitstatistiken abzurufen.

Weitere Informationen zum Leistungsmehraufwand bei der Abfrageprofilerstellung finden Sie im Blogbeitrag Developers Choice: Query progress – anytime, anywhere.

Hinweis

Erweiterte Ereignisse, die die einfache Profilerstellung nutzen, verwenden die Informationen der Standardprofilerstellung, wenn die Standardprofilerstellungsinfrastruktur bereits aktiviert ist. Dies tritt beispielsweise auf, wenn eine Sitzung mit dem erweiterten Ereignis query_post_execution_showplan ausgeführt und eine weitere Sitzung mit query_post_execution_plan_profile gestartet wird. Die zweite Sitzung verwendet weiterhin die Informationen der Standardprofilerstellung.

Hinweis

In SQL Server 2017 (14.x) ist die Lightweight-Profilerstellung standardmäßig deaktiviert, sie wird jedoch aktiviert, wenn eine XEvent-Ablaufverfolgung mit query_post_execution_plan_profile gestartet wird. Sie wird wieder deaktiviert, wenn die Ablaufverfolgung beendet wird. Daher wird dringend empfohlen, die Lightweight-Profilerstellung mit dem Ablaufverfolgungsflag 7412 global zu aktivieren, wenn auf query_post_execution_plan_profile basierende XEvent-Ablaufverfolgungen häufig für eine SQL Server 2017 (14.x)-Instanz gestartet und beendet werden, um den Mehraufwand durch die wiederholte Aktivierung/Deaktivierung zu vermeiden.

Weitere Informationen

Überwachen und Optimieren der Leistung
Tools für die Leistungsüberwachung und -optimierung
Öffnen des Aktivitätsmonitors (SQL Server Management Studio)
Aktivitätsmonitor
Überwachen der Leistung mit dem Abfragespeicher
Überwachen der Systemaktivität mit erweiterten Ereignissen
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Ablaufverfolgungsflags
Referenz zu logischen und physischen Showplanoperatoren
Tatsächlicher Ausführungsplan
Live-Abfragestatistik

Freigeben über

Profilerstellungsinfrastruktur für Abfragen

Standard-Profilerstellungsinfrastruktur für Abfrageausführungsstatistiken

Die einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v1

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v2

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v3

Beispiel 1: Erweiterte Ereignissitzung mit der Standardprofilerstellung

Beispiel 2: Erweiterte Ereignissitzung mit der einfachen Profilerstellung

Leitfaden für die Verwendung der Profilerstellungsinfrastruktur für Abfragen

Hinweise

Weitere Informationen

Feedback

Zusätzliche Ressourcen