Monitoraggio delle prestazioni di stored procedure compilate in modo nativo
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 database XTP_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 database XTP_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.