Überwachen der Leistung von systemintern kompilierten gespeicherten Prozeduren

Artikel
08/23/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Artikel wird erläutert, wie Sie die Leistung von nativ kompilierten gespeicherten Prozeduren und anderen nativ kompilierten T-SQL-Modulen überwachen können.

Unter Verwendung erweiterter Ereignisse

Verwenden Sie das erweiterte Ereignis sp_statement_completed , um die Ausführung einer Abfrage zu verfolgen. Erstellen Sie eine Sitzung für erweiterte Ereignisse mit diesem Ereignis. Optional können Sie für eine bestimmte nativ kompilierte gespeicherte Prozedur nach object_id filtern. Das erweiterte Ereignis wird nach der Ausführung jeder Abfrage ausgelöst. Die vom erweiterten Ereignis angegebene CPU-Zeit und Dauer geben an, wie lange die CPU genutzt und wie lange die Abfrage ausgeführt wurde. Bei einer systemintern kompilierten gespeicherten Prozedur, die viel CPU-Zeit beansprucht, treten u. U. Leistungsprobleme auf.

Nebenline_numberkann auch object_id im erweiterten Ereignis verwendet werden, um die Abfrage zu untersuchen. Mithilfe der folgenden Abfrage kann die Prozedurdefinition abgerufen werden. Anhand der Zeilennummer wird die Abfrage innerhalb der Definition identifiziert:

SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;

Verwenden von Datenverwaltungsansichten und Abfragespeicher

SQL Server und Azure SQL-Datenbank unterstützen das Sammeln von Ausführungsstatistiken für nativ kompilierte gespeicherte Prozeduren sowohl auf Prozedurebene als auch auf der Abfrageebene. Das Sammeln statistischer Ausführungsdaten ist aufgrund der Leistungsauswirkungen standardmäßig nicht aktiviert.

Ausführungsstatistiken werden in den Systemansichten sys.dm_exec_procedure_stats und sys.dm_exec_query_stats sowie im Abfragespeicher wiedergegeben.

Ausführungsstatistiken auf Prozedurebene

SQL Server: Aktivieren oder Deaktivieren der Statistikauflistung für nativ kompilierte gespeicherte Prozeduren auf Prozedurebene mithilfe von sys.sp_xtp_control_proc_exec_stats (Transact-SQL). Die folgende Anweisung aktiviert die Sammlung von Ausführungsstatistiken auf Prozedurebene für alle nativ kompilierten T-SQL-Module auf der aktuellen Instanz:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL-Datenbank und SQL Server: Aktivieren oder Deaktivieren der Statistikauflistung für nativ kompilierte gespeicherte Prozeduren auf Prozedurebene mithilfe der KonfigurationsoptionXTP_PROCEDURE_EXECUTION_STATISTICSmit Datenbankbereich. Die folgende Anweisung aktiviert die Sammlung von Ausführungsstatistiken auf Prozedurebene für alle nativ kompilierten T-SQL-Module auf der aktuellen Datenbank:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Ausführungsstatistiken auf Abfrageebene

SQL Server: Aktivieren oder Deaktivieren der Statistikauflistung für nativ kompilierte gespeicherte Prozeduren auf Abfrageebene mithilfe von sys.sp_xtp_control_query_exec_stats (Transact-SQL). Die folgende Anweisung aktiviert die Sammlung von Ausführungsstatistiken auf Abfrageebene für alle nativ kompilierten T-SQL-Module auf der aktuellen Instanz:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL-Datenbank und SQL Server: Aktivieren oder Deaktivieren der Statistikauflistung für nativ kompilierte gespeicherte Prozeduren auf Anweisungsebene mithilfe der KonfigurationsoptionXTP_QUERY_EXECUTION_STATISTICSmit Datenbankbereich. Die folgende Anweisung aktiviert die Sammlung von Ausführungsstatistiken auf Abfrageebene für alle nativ kompilierten T-SQL-Module auf der aktuellen Datenbank:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Beispielabfragen

Nachdem Sie Statistiken gesammelt haben, können die Ausführungsstatistiken für nativ kompilierte gespeicherte Prozeduren mit sys.dm_exec_procedure_stats (Transact-SQL) und für Abfragen mit sys.dm_exec_query_stats (Transact-SQL) abgefragt werden.

Mit der folgenden Abfrage werden nach der Statistiksammlung die Prozedurnamen und Ausführungsstatistiken für systemintern kompilierte gespeicherte Prozeduren in der aktuellen Datenbank zurückgegeben:

SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

Mit der folgenden Abfrage werden der Abfragetext und Ausführungsstatistiken für alle Abfragen in systemintern kompilierten gespeicherten Prozeduren der aktuellen Datenbank zurückgegeben, für die statistische Daten gesammelt wurden. Die Ergebnisse sind nach total_worker_time in absteigender Reihenfolge sortiert:

SELECT st.objectid,
        OBJECT_NAME(st.objectid) AS 'object name',
        SUBSTRING(
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) AS 'query text',
        qs.creation_time, qs.last_execution_time, qs.execution_count,
        qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, 
        qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
        qs.min_elapsed_time, qs.max_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

Abfragen von Ausführungsplänen

Systemintern kompilierte gespeicherte Prozeduren unterstützen SHOWPLAN_XML (geschätzter Ausführungsplan). Der geschätzte Ausführungsplan kann verwendet werden, um den Abfrageplan auf Planungsfehler zu überprüfen. Häufige Gründe für fehlerhafte Pläne sind:

Statistiken wurden vor der Erstellung der Prozedur nicht aktualisiert.
Fehlende Indizes

Showplan-XML wird durch Ausführen der folgenden Transact-SQL abgerufen:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

Wählen Sie alternativ in SQL Server Management Studio den Prozedurnamen aus, und klicken Sie auf " Geschätzter Ausführungsplan anzeigen".

Im geschätzten Ausführungsplan für systemintern kompilierte gespeicherte Prozeduren werden die Abfrageoperatoren und Ausdrücke für die Abfragen der Prozedur angezeigt. SQL Server 2014 (12.x) unterstützt nicht alle SHOWPLAN_XML Attribute für nativ kompilierte gespeicherte Prozeduren. Attribute in Zusammenhang mit Kostenberechnungen des Abfrageoptimierers sind nicht Teil der SHOWPLAN_XML für die Prozedur.

Weitere Informationen

Nativ kompilierte gespeicherte Prozeduren