Natívan lefordított tárolt eljárások teljesítményének figyelemmel kísérése

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Felügyelt Példány

Ez a cikk bemutatja, hogyan figyelheti a natívan lefordított tárolt eljárások és más natívan lefordított T-SQL-modulok teljesítményét.

Bővített események használata

A lekérdezés végrehajtásának nyomon követéséhez használja a sp_statement_completed kiterjesztett eseményt. Hozzon létre egy kiterjesztett esemény munkamenetet ezzel az eseménysel, opcionálisan egy szűrővel a object_id egy adott natívan lefordított tárolt eljáráshoz. A kiterjesztett esemény az egyes lekérdezések végrehajtása után következik be. A kiterjesztett esemény által jelentett processzoridő és időtartam azt jelzi, hogy a lekérdezés mennyi processzorhasználatot és végrehajtási időt használ. Egy natívan lefordított tárolt eljárás, amely sok processzoridőt használ, teljesítményproblémákhoz vezethet.

A line_number, és a object_id a kiterjesztett eseményben is használható a lekérdezés vizsgálatához. Az eljárásdefiníció lekéréséhez az alábbi lekérdezés használható. A sorszám a definíción belüli lekérdezés azonosítására használható:

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

Adatkezelési nézetek és lekérdezéstár használata

Az SQL Server és az Azure SQL Database támogatja a natívan lefordított tárolt eljárások végrehajtási statisztikáinak gyűjtését mind az eljárás, mind a lekérdezés szintjén. A végrehajtási statisztikák gyűjtése alapértelmezés szerint nem engedélyezett a teljesítményre gyakorolt hatás miatt.

A végrehajtási statisztikák megjelennek a rendszernézetekben sys.dm_exec_procedure_stats és sys.dm_exec_query_stats, valamint Lekérdezéstár.

Procedure-Level végrehajtási statisztikák

SQL Server: A natívan lefordított tárolt eljárások statisztikai gyűjtésének engedélyezése vagy letiltása eljárásszinten a sys.sp_xtp_control_proc_exec_stats (Transact-SQL)használatával. Az alábbi utasítás lehetővé teszi az eljárásszintű végrehajtási statisztikák gyűjtését az aktuális példányon natívan lefordított T-SQL-modulokhoz:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL Database és SQL Server: A natívan lefordított tárolt eljárások statisztikai gyűjtésének engedélyezése vagy letiltása az eljárás szintjén az adatbázis-hatókörű konfigurációs beállítás XTP_PROCEDURE_EXECUTION_STATISTICShasználatával. Az alábbi utasítás lehetővé teszi az eljárásszintű végrehajtási statisztikák gyűjtését az aktuális adatbázis összes natívan lefordított T-SQL-moduljára vonatkozóan:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Query-Level végrehajtási statisztikák

SQL Server: A lekérdezés szintjén natívan lefordított tárolt eljárások statisztikai gyűjtésének engedélyezése vagy letiltása sys.sp_xtp_control_query_exec_stats (Transact-SQL)használatával. Az alábbi utasítás lehetővé teszi a lekérdezésszintű végrehajtási statisztikák gyűjtését az aktuális példányon natívan lefordított T-SQL-modulokhoz:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL Database és SQL Server: A adatbázis-hatókörű konfigurációs beállítás XTP_QUERY_EXECUTION_STATISTICShasználatával engedélyezheti vagy tilthatja le a natívan lefordított tárolt eljárások statisztikai gyűjtését az utasítás szintjén. Az alábbi utasítás lehetővé teszi a lekérdezésszintű végrehajtási statisztikák gyűjtését az aktuális adatbázisban natívan lefordított T-SQL-modulokhoz:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Minta lekérdezések

A statisztikák gyűjtése után a natívan lefordított tárolt eljárások végrehajtási statisztikái lekérdezhetők a sys.dm_exec_procedure_stats (Transact-SQL), valamint a sys.dm_exec_query_stats (Transact-SQL)lekérdezésekhez.

Az alábbi lekérdezés az aktuális adatbázisban natívan lefordított tárolt eljárások eljárásneveit és végrehajtási statisztikáit adja vissza a statisztikák gyűjtése után:

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;

Az alábbi lekérdezés visszaadja a lekérdezés szövegét és a végrehajtási statisztikákat az aktuális adatbázisban natívan lefordított tárolt eljárásokban lévő összes lekérdezéshez, amelyekhez a statisztikát a teljes munkavégző idő szerint rendezték csökkenő sorrendben:

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;

Lekérdezés-végrehajtási tervek

A natívan lefordított tárolt eljárások támogatják a SHOWPLAN_XML (becsült végrehajtási terv). A becsült végrehajtási terv segítségével megvizsgálhatja a lekérdezési tervet, és megkeresheti a rossz tervekkel kapcsolatos problémákat. A rossz tervek gyakori okai a következők:

• A statisztikák nem frissültek az eljárás létrehozása előtt.

• Hiányzó indexek

A Showplan XML a következő Transact-SQL végrehajtásával érhető el:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO  

Másik lehetőségként az SQL Server Management Studióban válassza ki az eljárás nevét, és kattintson Becsült végrehajtási terv megjelenítéseelemre.

A natívan lefordított tárolt eljárások becsült végrehajtási terve az eljárás lekérdezési operátorait és kifejezéseit jeleníti meg. Az SQL Server 2014 (12.x) nem támogatja a natívan lefordított tárolt eljárások SHOWPLAN_XML attribútumait. A lekérdezésoptimalizáló költségszámításához kapcsolódó attribútumok például nem részei az eljárásban lévő SHOWPLAN_XML-nek.

Lásd még:

Natív módon lefordított tárolt eljárások