Övervaka prestanda för internt kompilerade lagrade procedurer

Artikel
2025-03-05

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

I den här artikeln beskrivs hur du kan övervaka prestanda för internt kompilerade lagrade procedurer och andra inbyggda kompilerade T-SQL-moduler.

Använda utökade händelser

Använd den utökade händelsen sp_statement_completed för att spåra exekveringen av en fråga. Skapa en utökad händelsesession med den här händelsen, om du vill med ett filter på object_id för en viss inbyggt kompilerad lagrad procedur. Den utökade händelsen utlöses efter körningen av varje fråga. CPU-tiden och varaktigheten som rapporterats av den utökade händelsen anger hur mycket CPU frågan använde och den totala körningstiden. En internt kompilerad lagrad procedur som använder mycket CPU-tid kan ha prestandaproblem.

line_number, tillsammans med object_id i den utökade händelsen kan användas för att undersöka frågan. Följande fråga kan användas för att hämta procedurdefinitionen. Radnumret kan användas för att identifiera frågan i definitionen:

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

Använda datahanteringsvyer och frågearkiv

SQL Server och Azure SQL Database stöder insamling av körningsstatistik för internt kompilerade lagrade procedurer, både på procedurnivå och frågenivå. Insamling av körningsstatistik är inte aktiverat som standard på grund av prestandapåverkan.

Körningsstatistiken återspeglas i systemvyerna sys.dm_exec_procedure_stats och sys.dm_exec_query_statssamt i Query Store.

Procedure-Level körningsstatistik

SQL Server: Aktivera eller inaktivera insamling av statistik på internt kompilerade lagrade procedurer på procedurnivå med hjälp av sys.sp_xtp_control_proc_exec_stats (Transact-SQL). Följande instruktion möjliggör insamling av körningsstatistik på procedurnivå för alla internt kompilerade T-SQL-moduler på den aktuella instansen:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL Database och SQL Server: Aktivera eller inaktivera insamling av statistik på internt kompilerade lagrade procedurer på procedurnivå med hjälp av databasomfattande konfiguration alternativet XTP_PROCEDURE_EXECUTION_STATISTICS. Följande instruktion möjliggör insamling av körningsstatistik på procedurnivå för alla internt kompilerade T-SQL-moduler i den aktuella databasen:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Query-Level körningsstatistik

SQL Server-: Aktivera eller inaktivera statistikinsamling på internt kompilerade lagrade procedurer på frågenivå med hjälp av sys.sp_xtp_control_query_exec_stats (Transact-SQL). Följande instruktion möjliggör insamling av körningsstatistik på frågenivå för alla internt kompilerade T-SQL-moduler på den aktuella instansen:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL Database och SQL Server: Aktivera eller inaktivera insamling av statistik på internt kompilerade lagrade procedurer på instruktionsnivå med hjälp av databasomfattande konfiguration alternativet XTP_QUERY_EXECUTION_STATISTICS. Följande instruktion möjliggör insamling av körningsstatistik på frågenivå för alla internt kompilerade T-SQL-moduler i den aktuella databasen:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Exempelfrågor

När du har samlat in statistik kan körningsstatistiken för nativt kompilerade lagrade procedurer efterfrågas för en procedur med sys.dm_exec_procedure_stats (Transact-SQL)och för frågestatistik med sys.dm_exec_query_stats (Transact-SQL).

Följande fråga returnerar procedurnamnen och körningsstatistiken för internt kompilerade lagrade procedurer i den aktuella databasen, efter statistikinsamling:

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;

Följande fråga returnerar frågetexten samt körningsstatistik för alla frågor i internt kompilerade lagrade procedurer i den aktuella databasen för vilken statistik har samlats in, sorterad efter total arbetstid, i fallande ordning:

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;

Frågeexekveringsplaner

Inhemskt kompilerade lagrade procedurer stöder SHOWPLAN_XML (uppskattad exekveringsplan). Den uppskattade körningsplanen kan användas för att inspektera frågeplanen för att hitta eventuella problem med en felaktig plan. Vanliga orsaker till dåliga planer är:

Statistiken uppdaterades inte innan proceduren skapades.
Index saknas

Showplan XML hämtas genom att köra följande Transact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

Du kan också välja procedurnamnet i SQL Server Management Studio och klicka på Visa beräknad exekveringsplan.

Den uppskattade körningsplanen för internt kompilerade lagrade procedurer visar frågeoperatorer och uttryck för frågorna i proceduren. SQL Server 2014 (12.x) stöder inte alla SHOWPLAN_XML attribut för internt kompilerade lagrade procedurer. Attribut som är relaterade till frågeoptimerarkostnad ingår till exempel inte i SHOWPLAN_XML för proceduren.

Se även

Inbyggt kompilerade lagrade procedurer

Dela via