Profilerstellungsinfrastruktur für Abfragen

Gilt für:SQL ServerAzure SQL-Datenbank

Das SQL Server-Datenbankmodul 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. Im Live-Abfrageplan werden die gesamten Abfragefortschritts- und Laufzeitausführungsstatistiken auf Operatorebene angezeigt, z. B. die Anzahl der erzeugten Zeilen, verstrichene Zeit, Operatorfortschritt usw. Da diese Daten in Echtzeit verfügbar sind, ohne warten zu müssen, bis die Abfrage abgeschlossen ist, sind diese Ausführungsstatistiken äußerst nützlich für das Debuggen von Abfrageleistungsproblemen, z. B. lange ausgeführte Abfragen und Abfragen, die unbegrenzt ausgeführt werden und nie abgeschlossen 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 query Statistics " in SQL Server Management Studio klicken, wird die standardmäßige Profilerstellungsinfrastruktur verwendet.
In höheren Versionen von SQL Server, wenn die einfache Profilerstellungsinfrastruktur aktiviert ist, wird sie von Liveabfragestatistiken anstelle von Standardprofilerstellung genutzt, wenn sie über den Aktivitätsmonitor angezeigt oder die sys.dm_exec_query_profiles DMV direkt abgefragt wird.

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

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 Infrastruktur für die Profilerstellung für einfache Abfrageausführungsstatistiken

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 bis SQL Server 2016 (13.x)).

Ab SQL Server 2014 (12.x) SP2 und SQL Server 2016 (13.x) wurde der Leistungsaufwand zum Sammeln von Informationen zu Ausführungsplänen mit der Einführung der einfachen Profilerstellung reduziert. 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 bis SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1 enthält eine überarbeitete Version der einfachen Profilerstellung mit minimalem Aufwand. 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, wenn einfache Profilerstellung nicht global aktiviert ist, kann das neue HINT-AbfragehinweisargumentQUERY_PLAN_PROFILE verwendet werden, um einfache Profilerstellung auf Abfrageebene für jede Sitzung zu ermöglichen. 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-Datenbank enthalten eine neu überarbeitete Version der einfachen Profilerstellung, die Zeilenanzahlinformationen für alle Ausführungen erfasst. Einfache Profilerstellung ist in SQL Server 2019 (15.x) und Azure SQL-Datenbank standardmäßig aktiviert. Ab SQL Server 2019 (15.x) hat das Ablaufverfolgungskennzeichnung 7412 keine Auswirkung. 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 zur Verwendung der Abfrageprofilinfrastruktur

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.

Bereich Standardprofilerstellung Lightweight-Profilerstellung
Global xEvent-Sitzung mit dem query_post_execution_showplan XE; Ab SQL Server 2012 (11.x) Spurkennzeichnung 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 Query Statistics " 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, wird aber aktiviert, wenn eine XEvent-Ablaufverfolgung gestartet wird, auf query_post_execution_plan_profile die sich verlassen wird, und wird dann erneut deaktiviert, wenn die Ablaufverfolgung beendet wird. Wenn Xevent-Ablaufverfolgungen, die auf query_post_execution_plan_profile einer SQL Server 2017 (14.x)-Instanz basieren, häufig gestartet und beendet werden, wird dringend empfohlen, lightweight Profiling auf globaler Ebene mit traceflag 7412 zu aktivieren, um den wiederholten Aktivierungs-/Deaktivierungsaufwand 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