Infrastruktur zur Profilierung von Abfragen

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Datenbank in Microsoft Fabric

Das SQL Server-Datenbankmodul bietet Zugriff auf Laufzeitinformationen für Abfrageausführungspläne. 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. Daher ist der Zugang zum eigentlichen 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.

Die Standard-Profiling-Infrastruktur für Abfrageausführungsstatistiken

Die Profilinfrastruktur der Abfrageausführungsstatistik oder die Standardprofilerstellung muss aktiviert sein, um Informationen zu Ausführungsplänen, nämlich Zeilenanzahl, CPU- und E/A-Nutzung, zu sammeln. Die folgenden Methoden zum Sammeln von Ausführungsplaninformationen für eine Zielsitzung verwenden die standardmäßige Profilerstellungsinfrastruktur:

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

Die folgenden Methoden zum globalen Sammeln von Ausführungsplaninformationen für alle Sitzungen verwenden die Standardmäßige Profilerstellungsinfrastruktur:

• Das query_post_execution_showplan erweiterte Ereignis. Informationen zum Aktivieren erweiterter Ereignisse finden Sie unter "Überwachen der Systemaktivität mithilfe erweiterter Ereignisse".
• Das Ablaufverfolgungsereignis Showplan XML in der SQL-Ablaufverfolgung und in SQL Server Profiler. Weitere Informationen zu diesem Ablaufverfolgungsereignis finden Sie unter Showplan XML Event Class.

Wenn Sie eine erweiterte Ereignissitzung ausführen, die das Ereignis query_post_execution_showplan verwendet, wird auch die sys.dm_exec_query_profiles-DMV aufgefüllt, wodurch Live-Abfrage-Statistiken für alle Sitzungen mithilfe des Aktivitätsmonitors oder durch direkte Abfrage der DMV ermöglicht werden. Weitere Informationen finden Sie unter Live Query Statistics.

Die leichtgewichtige Infrastruktur zur Profilierung von 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.

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v1

Gilt für: 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 standardmäßigen Profilerstellung sammelt die einfache Profilerstellung keine CPU-Laufzeitinformationen. Allerdings erfasst die einfache Profilerstellung weiterhin die Zeilenanzahl und Informationen zur E/A-Verwendung.

Außerdem wurde auch ein neues query_thread_profile Extended Event eingeführt, das ressourcenschonendes Profiling verwendet. 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
);

Beim Ausführen einer Sitzung für erweiterte Ereignisse, die das Ereignis query_thread_profile verwendet, wird auch die DMV sys.dm_exec_query_profiles mithilfe von Lightweight-Profiling aufgefüllt, wodurch Live-Abfragestatistiken für alle Sitzungen über den Aktivitätsmonitor oder durch direkte Abfrage der DMV ermöglicht werden.

Einfache Profilerstellungsinfrastruktur für die Abfrageausführungsstatistik v2

Gilt für: 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. Leichtgewichtiges Profiling kann auch weltweit mithilfe des Ablaufverfolgungskennzeichen 7412 für die zuvor in Gilt für aufgeführten Versionen aktiviert werden. Eine neue DMF sys.dm_exec_query_statistics_xml wurde eingeführt, um den Abfrageausführungsplan für In-Flight-Anforderungen zurückzugeben.

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

Eine Beispielsitzung mit dem query_plan_profile erweiterten Ereignis kann wie im folgenden 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 2019 (15.x) und höhere Versionen sowie Azure SQL-Datenbank

SQL Server 2019 (15.x) und Azure SQL-Datenbank 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-Datenbank aktiviert. In SQL Server 2019 (15.x) und späteren Versionen hat das Trace-Flag 7412 keine Auswirkung. Leichtgewichtiges Profiling kann auf Datenbankebene mithilfe der LIGHTWEIGHT_QUERY_PROFILINGdatenbankbezogenen Konfiguration deaktiviert werden: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Es wird eine neue DMF sys.dm_exec_query_plan_stats eingeführt, um für die meisten Abfragen das Äquivalent des zuletzt bekannten tatsächlichen Ausführungsplans zurückzugeben, und sie wird als Statistiken des letzten Abfrageplans bezeichnet. Die letzte Abfrageplanstatistik kann mithilfe der LAST_QUERY_PLAN_STATSDatenbankbereichskonfiguration auf Datenbankebene aktiviert werden: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

Ein neues query_post_execution_plan_profile Extended Event erfasst auf der Grundlage von leichtgewichtigem Profiling das Äquivalent eines tatsächlichen Ausführungsplans, anders als query_post_execution_showplan, das standardmäßiges Profiling verwendet. SQL Server 2017 (14.x) bietet dieses Ereignis ab CU14 ebenfalls an. Eine Beispielsitzung mit dem query_post_execution_plan_profile erweiterten Ereignis kann wie im folgenden Beispiel konfiguriert werden:

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 – Extended Events-Sitzung mit Leichtgewichtsprofiling

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

Hinweise zur Verwendung der Infrastruktur für die Abfrageprofilierung

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. Enthält auch die früheste Version, für die die Aktion verfügbar ist.

Remarks

Beginnend mit Lightweight Profiling v2 und seiner geringen Zusatzlast kann jeder Server, der nicht bereits CPU-gebunden ist, leichtgewichtiges Profiling kontinuierlich ausführen und Datenbankfachleuten ermöglichen, jederzeit auf jede laufende Ausführung zuzugreifen, z. B. mit Activity Monitor oder durch direkte Abfrage 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.

Erweiterte Ereignisse, die leichtgewichtiges Profiling verwenden, greifen auf Informationen aus dem Standardprofiling zurück, falls die Infrastruktur für das Standardprofiling 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 Informationen aus der Standardprofilerstellung.

Verwandte Inhalte

• Überwachen und Optimieren der Leistung
• Leistungsüberwachungs- und Optimierungstools
• Öffnen des Aktivitätsmonitors in SQL Server Management Studio (SSMS)
• Aktivitätsmonitor
• Überwachen der Leistung mithilfe des Abfragespeichers
• Überwachen der Systemaktivität mit erweiterten Ereignissen
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• Festlegen von Traceflags mit DBCC TRACEON (Transact-SQL)
• Referenz zu logischen und physischen Showplan-Operatoren
• Tatsächlicher Ausführungsplan
• Live-Abfragestatistik