Profilerstellungsinfrastruktur für Abfragen

Anwendungsbereich: JaSQL Server (alle unterstützten Versionen) JaAzure 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, ohne auf den Abschluss der Abfrage warten zu müssen, sind diese Ausführungsstatistiken äußerst nützlich für das Debuggen von Leistungsproblemen bei Abfragen, beispielsweise bei Abfragen mit langer Ausführungszeit und Abfragen, die unbegrenzt ausgeführt 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-Abfragestatistik einschließen in SQL Server Management Studio klicken, wird die Standard-Profilerstellungsinfrastruktur verwendet.
Ist die Lightweight-Infrastruktur zur Profilerstellung in höheren Versionen von SQL Server aktiviert, wird sie von Live-Abfragestatistiken anstelle der Standardprofilerstellung verwendet, wenn diese über den Aktivitätsmonitor oder durch direktes Abfragen der sys.dm_exec_query_profiles-DMV angezeigt werden.

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 einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik

Beginnend mit SQL Server 2014 (12.x) SP2 und SQL Server 2016 (13.x) wurde eine neue einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik (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))

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 (Von Entwicklern inspiriert: Abfragestatus – jederzeit und überall)

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 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 abgeschlossen wird, die diesen neuen Hinweis enthält, 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 Informationen zur Anzahl der Zeilen 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 Ablaufverfolgungsflag 7412 keine Auswirkungen. 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. In SQL Server 2017 (14.x) ist dieses Ereignis ab CU14 ebenfalls enthalten. 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 Infrastruktur zur Abfrageprofilerstellung

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 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 query_thread_profile XE, ab SQL Server 2014 (12.x) SP2
Global - xEvent-Sitzung mit 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 Verwendung des QUERY_PLAN_PROFILE-Abfragehinweises zusammen mit einer xEvent-Sitzung mit 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 Klick auf die Schaltfläche Live-Abfragestatistik in SSMS, ab SQL Server 2014 (12.x) SP2 -

Bemerkungen

Wichtig

Aufgrund einer möglichen zufälligen AV bei der Ausführung einer gespeicherten Überwachungsprozedur, die auf sys.dm_exec_query_statistics_xml verweist, müssen Sie sicherstellen, 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 (Von Entwicklern inspiriert: Abfragestatus – jederzeit und überall)

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