Profilerstellungsinfrastruktur für Abfragen

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:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Live-Abfragestatistik

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.

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

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.

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.

Hinweise

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.

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