Freigeben über

Profilerstellungsinfrastruktur für 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.

Standard-Profilerstellungsinfrastruktur 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:

Note

Wenn Sie die Schaltfläche " Live query Statistics" in SQL Server Management Studio auswählen, wird die standardmäßige Profilerstellungsinfrastruktur verwendet. In späteren Versionen von SQL Server, wenn die einfache Profilerstellungsinfrastruktur aktiviert ist, wird sie von Liveabfragestatistiken anstelle von Standardprofilerstellung verwendet, wenn sie über den Aktivitätsmonitor angezeigt oder die sys.dm_exec_query_profiles DMV direkt abgefragt wird.

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

Wenn Sie eine erweiterte Ereignissitzung ausführen, die das Ereignis verwendet, wird auch die query_post_execution_showplan DMV aufgefüllt, wodurch Liveabfragestatistiken für alle Sitzungen mithilfe des Aktivitätsmonitors oder direkt beim Abfragen des DMV aktiviert werden. 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.

Note

Nativ kompilierte gespeicherte Prozeduren werden mit einfacher Profilerstellung nicht unterstützt.

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 ein neues query_thread_profile erweitertes Ereignis eingeführt, das einfache Profilerstellung 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
);

Note

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

Beim Ausführen einer erweiterten Ereignissitzung, die das Ereignis verwendet, wird der query_thread_profile DMV auch mithilfe einer einfachen Profilerstellung aufgefüllt, wodurch Liveabfragestatistiken für alle Sitzungen, mithilfe des Aktivitätsmonitors oder der direkten Abfrage des 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 das neue HINT-AbfragehinweisargumentQUERY_PLAN_PROFILE für jede Sitzung verwendet werden, wenn einfache Profilerstellung nicht global aktiviert ist. 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.

Note

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

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 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. In SQL Server 2019 (15.x) und späteren Versionen hat das Trace-Flag 7412 keine Auswirkung. Einfache Profilerstellung kann mithilfe der LIGHTWEIGHT_QUERY_PROFILINGDatenbankbereichskonfiguration 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 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 erweitertes Ereignis sammelt das Äquivalent eines tatsächlichen Ausführungsplans basierend auf der einfachen Profilerstellung, anders als query_post_execution_showplanbei der Verwendung von Standardprofilerstellung. 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: 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 Einfache Profilerstellung
Global Erweiterte Ereignissitzung mit dem query_post_execution_showplan XE; Ab SQL Server 2012 (11.x) Spurkennzeichnung 7412; Ab SQL Server 2016 (13.x) SP1
Global SQL Trace und SQL Server Profiler mit dem Showplan XML Ablaufverfolgungsereignis Erweiterte Ereignissitzung mit dem query_thread_profile XE; Ab SQL Server 2014 (12.x) SP2
Global N/A Erweiterte Ereignissitzung mit dem query_post_execution_plan_profile XE; Ab SQL Server 2017 (14.x) CU14 und SQL Server 2019 (15.x)
Session Verwende SET STATISTICS XML ON Verwenden Sie den QUERY_PLAN_PROFILE Abfragehinweis zusammen mit einer erweiterten Ereignissitzung mit dem query_plan_profile XE; Ab SQL Server 2016 (13.x) SP2 CU3 und SQL Server 2017 (14.x) CU11
Session Verwende SET STATISTICS PROFILE ON N/A
Session Wählen Sie die Schaltfläche "Live Query Statistics " in SSMS aus. Ab SQL Server 2014 (12.x) SP2 N/A

Remarks

Important

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 dem geringen Mehraufwand kann jeder Server, der noch nicht CPU-gebunden ist, kontinuierlich einfache Profilerstellung ausführen und Datenbankexperten ermöglichen, jederzeit eine laufende Ausführung durchzuführen, z. B. mithilfe des Aktivitätsmonitors oder der direkten Abfrage sys.dm_exec_query_profiles, und rufen Sie den Abfrageplan mit Laufzeitstatistiken ab.

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

Erweiterte Ereignisse, die einfache Profilerstellung verwenden, verwenden Informationen aus der Standardprofilerstellung, falls die Standardmäßige Profilerstellungsinfrastruktur 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.

Note

In SQL Server 2017 (14.x) ist die Lightweight-Profilerstellung standardmäßig deaktiviert, wird aber aktiviert, wenn eine erweiterte Ereignisablaufverfolgung gestartet wird, die auf den query_post_execution_plan_profile Start setzt, und wird dann erneut deaktiviert, wenn die Ablaufverfolgung beendet wird. Wenn erweiterte Ereignisablaufverfolgungen basierend auf query_post_execution_plan_profile daher häufig auf einer SQL Server 2017 (14.x)-Instanz gestartet und beendet werden, sollten Sie Lightweight-Profiling auf globaler Ebene mit der Ablaufverfolgungskennzeichnung 7412 aktivieren, um den wiederholten Aktivierungs-/Deaktivierungsaufwand zu vermeiden.

Verwandte Inhalte

  • Last updated on