Prestaties van systeemeigen gecompileerde opgeslagen procedures bewaken

Artikel
2025-03-05

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

In dit artikel wordt beschreven hoe u de prestaties van systeemeigen opgeslagen procedures en andere systeemeigen gecompileerde T-SQL-modules kunt bewaken.

Uitgebreide gebeurtenissen gebruiken

Gebruik de sp_statement_completed uitgebreide gebeurtenis om de uitvoering van een query te traceren. Maak een uitgebreide gebeurtenissessie met deze gebeurtenis, eventueel met een filter op object_id voor een bepaalde systeemeigen gecompileerde opgeslagen procedure. De uitgebreide gebeurtenismelding wordt opgeroepen na de uitvoering van elke query. De CPU-tijd en duur die door de uitgebreide gebeurtenis zijn gerapporteerd, geven aan hoeveel CPU de query heeft gebruikt en wat de uitvoeringstijd was. Een systeemeigen gecompileerde opgeslagen procedure die veel CPU-tijd gebruikt, kan prestatieproblemen hebben.

De line_number, samen met de object_id in de uitgebreide gebeurtenis, kan worden gebruikt om de query te onderzoeken. De volgende query kan worden gebruikt om de proceduredefinitie op te halen. Het regelnummer kan worden gebruikt om de query binnen de definitie te identificeren:

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

Gegevensbeheerweergaven en Query Store gebruiken

SQL Server en Azure SQL Database ondersteunen het verzamelen van uitvoeringsstatistieken voor systeemeigen gecompileerde opgeslagen procedures, zowel op procedureniveau als op queryniveau. Het verzamelen van uitvoeringsstatistieken is niet standaard ingeschakeld vanwege invloed op de prestaties.

Uitvoeringsstatistieken worden weergegeven in de systeemweergaven sys.dm_exec_procedure_stats en sys.dm_exec_query_stats, evenals in Query Store.

Procedure-Level uitvoeringsstatistieken

SQL Server-: statistiekverzameling in- of uitschakelen voor natuurlijk gecompileerde opgeslagen procedures op procedureniveau met sys.sp_xtp_control_proc_exec_stats (Transact-SQL). De volgende instructie maakt het verzamelen van uitvoeringsstatistieken op procedureniveau mogelijk voor alle systeemeigen gecompileerde T-SQL-modules op het huidige exemplaar:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL Database en SQL Server-: het verzamelen van statistieken in- of uitschakelen op systeemeigen opgeslagen procedures op procedureniveau met behulp van de configuratieoptie optie XTP_PROCEDURE_EXECUTION_STATISTICS. Met de volgende instructie kunt u uitvoeringsstatistieken op procedureniveau verzamelen voor alle systeemeigen gecompileerde T-SQL-modules in de huidige database:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Query-Level uitvoeringsstatistieken

SQL Server-: het verzamelen van statistieken op systeemeigen opgeslagen procedures op queryniveau in- of uitschakelen met behulp van sys.sp_xtp_control_query_exec_stats (Transact-SQL). De volgende instructie maakt het verzamelen van uitvoeringsstatistieken op queryniveau mogelijk voor alle systeemeigen gecompileerde T-SQL-modules op het huidige exemplaar:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL Database en SQL Server: Het in- of uitschakelen van het verzamelen van statistieken op natuurlijk gecompileerde opgeslagen procedures op instructieniveau met behulp van de database-brede configuratieoptieXTP_QUERY_EXECUTION_STATISTICS. De volgende instructie maakt het verzamelen van uitvoeringsstatistieken op queryniveau mogelijk voor alle systeemeigen gecompileerde T-SQL-modules in de huidige database:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Voorbeeldquery's

Nadat u statistieken hebt verzameld, kunnen de uitvoeringsstatistieken voor systeemeigen opgeslagen procedures worden opgevraagd voor een procedure met sys.dm_exec_procedure_stats (Transact-SQL)en voor query's met sys.dm_exec_query_stats (Transact-SQL).

De volgende query retourneert de procedurenamen en uitvoeringsstatistieken voor systeemeigen gecompileerde opgeslagen procedures in de huidige database, na verzameling statistieken:

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;

De volgende query retourneert de querytekst en uitvoeringsstatistieken voor alle query's in systeemeigen gecompileerde opgeslagen procedures in de huidige database waarvoor statistieken zijn verzameld, gesorteerd op totale werktijd, in aflopende volgorde:

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;

Query-uitvoeringsplannen

Systeemeigen gecompileerde opgeslagen procedures ondersteunen SHOWPLAN_XML (geschatte uitvoeringsplan). Het geschatte uitvoeringsplan kan worden gebruikt om het queryplan te inspecteren, om eventuele problemen met slechte plannen te vinden. Veelvoorkomende redenen voor slechte plannen zijn:

Statistieken zijn niet bijgewerkt voordat de procedure werd gemaakt.
Ontbrekende indexen

Showplan XML wordt verkregen door de volgende Transact-SQL uit te voeren:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

U kunt ook in SQL Server Management Studio de naam van de procedure selecteren en op de knop Schat uitvoeringsplan weergevenklikken.

Het geschatte uitvoeringsplan voor systeemeigen gecompileerde opgeslagen procedures toont de queryoperators en expressies voor de query's in de procedure. SQL Server 2014 (12.x) biedt geen ondersteuning voor alle SHOWPLAN_XML kenmerken voor systeemeigen gecompileerde opgeslagen procedures. Kenmerken met betrekking tot kosten voor queryoptimalisatie maken bijvoorbeeld geen deel uit van de SHOWPLAN_XML voor de procedure.

Zie ook

systeemeigen gecompileerde opgeslagen procedures

Delen via