Monitoraggio delle prestazioni di stored procedure compilate in modo nativo

Articolo
03/12/2024

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo illustra come monitorare le prestazioni di stored procedure compilate in modo nativo e di altri moduli T-SQL compilati in modo nativo.

Utilizzo degli eventi estesi

Usare l'evento esteso sp_statement_completed per tracciare l'esecuzione di una query. Creare una sessione di eventi estesi con questo evento, applicando facoltativamente un filtro a OBJECT_ID per una stored procedure specifica compilata in modo nativo. L'evento esteso viene generato dopo l'esecuzione di ogni query. Il tempo e la durata della CPU segnalati dagli eventi estesi indicano la quantità di CPU utilizzata dalla query e il tempo di esecuzione. Una stored procedure compilata in modo nativo che utilizza un tempo di CPU elevato può presentare problemi di prestazioni.

È possibile usare line_number con object_id nell'evento esteso per esaminare la query. È possibile utilizzare la query seguente per recuperare la definizione della routine. Il numero di riga può essere utilizzato per identificare la query all'interno della definizione:

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

Uso di viste di Gestione dati e di Query Store

SQL Server e il database SQL di Azure supportano la raccolta delle statistiche di esecuzione per le stored procedure compilate in modo nativo, sia a livello di routine che a livello di query. La raccolta delle statistiche di esecuzione non è abilitata per impostazione predefinita a causa dell'impatto sulle prestazioni.

Le statistiche di esecuzione si riflettono nelle viste di sistema sys.dm_exec_procedure_stats e sys.dm_exec_query_stats, nonché in Query Store.

Statistiche di esecuzione a livello di routine

SQL Server: abilita o disabilita la raccolta di statistiche sulle stored procedure compilate in modo nativo a livello di routine tramite sys.sp_xtp_control_proc_exec_stats (Transact-SQL). L'istruzione seguente abilita la raccolta di statistiche di esecuzione a livello di routine per tutti i moduli T-SQL compilati in modo nativo nell'istanza corrente:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Database SQL di Azure e SQL Server: abilita o disabilita la raccolta di statistiche sulle stored procedure compilate in modo nativo a livello di routine tramite l'opzione di configurazione con ambito databaseXTP_PROCEDURE_EXECUTION_STATISTICS. L'istruzione seguente abilita la raccolta di statistiche di esecuzione a livello di routine per tutti i moduli T-SQL compilati in modo nativo nel database corrente:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Statistiche di esecuzione a livello di query

SQL Server: abilita o disabilita la raccolta di statistiche sulle stored procedure compilate in modo nativo a livello di query tramite sys.sp_xtp_control_query_exec_stats (Transact-SQL). L'istruzione seguente abilita la raccolta di statistiche di esecuzione a livello di query per tutti i moduli T-SQL compilati in modo nativo nell'istanza corrente:

EXEC sys.sp_xtp_control_query_exec_stats 1

Database SQL di Azure e SQL Server: abilita o disabilita la raccolta di statistiche sulle stored procedure compilate in modo nativo a livello di istruzione tramite l'opzione di configurazione con ambito databaseXTP_QUERY_EXECUTION_STATISTICS. L'istruzione seguente abilita la raccolta di statistiche di esecuzione a livello di query per tutti i moduli T-SQL compilati in modo nativo nel database corrente:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Query di esempio

Dopo avere raccolto le statistiche, è possibile interrogare le statistiche di esecuzione delle stored procedure compilate in modo nativo per individuare una routine con sys.dm_exec_procedure_stats (Transact-SQL) e per individuare query con sys.dm_exec_query_stats (Transact-SQL).

La query seguente restituisce i nomi delle routine e le statistiche di esecuzione per le stored procedure compilate in modo nativo nel database corrente, dopo la raccolta delle statistiche:

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;

La query seguente restituisce il testo della query nonché le statistiche di esecuzione per tutte le query nelle stored procedure compilate in modo nativo nel database corrente per il quale sono state raccolte le statistiche, ordinate in base al tempo del processo, in ordine decrescente.

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;

Piani di esecuzione di query

Le stored procedure compilate in modo nativo supportano SHOWPLAN_XML (piano di esecuzione stimato). Il piano di esecuzione stimato può essere utilizzato per esaminare il piano di query al fine di rilevare eventuali problemi relativi a piani non validi. I motivi comuni per i piani non validi sono:

Le statistiche non sono state aggiornate prima della creazione della routine.
Indici mancanti

Showplan XML viene ottenuto mediante l'esecuzione dell'istruzione Transact-SQL seguente:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

In alternativa, in SQL Server Management Studio seleziona il nome della routine e fai clic su Visualizza piano di esecuzione stimato.

Nel piano di esecuzione stimato per le stored procedure compilate in modo nativo vengono illustrati gli operatori e le espressioni delle query per le query nella routine. SQL Server 2014 (12.x) non supporta tutti gli attributi di SHOWPLAN_XML per le stored procedure compilate in modo nativo. Ad esempio, gli attributi correlati ai costi di Query Optimizer non fanno parte di SHOWPLAN_XML per la routine.

Vedi anche

Stored procedure compilate in modo nativo