Monitoraggio delle prestazioni di stored procedure compilate in modo nativoMonitoring Performance of Natively Compiled Stored Procedures

In questo argomento viene illustrato come è possibile monitorare le prestazioni di stored procedure compilate in modo nativoThis topic discusses how you can monitor the performance of natively compiled stored procedures

Utilizzo degli eventi estesiUsing Extended Events

Usare l'evento esteso sp_statement_completed per tracciare l'esecuzione di una query.Use the sp_statement_completed extended event to trace execution of a query. Creare una sessione di eventi estesi con questo evento, applicando facoltativamente un filtro a OBJECT_ID per una stored procedure compilata in modo nativo. L'evento esteso viene generato dopo l'esecuzione di ogni query.Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure, The extended event is raised after the execution of each 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.The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. Una stored procedure compilata in modo nativo che utilizza un tempo di CPU elevato può presentare problemi di prestazioni.A natively compiled stored procedure that uses a lot of CPU time may have performance problems.

È possibile usareline_numbercon object_id nell'evento esteso per esaminare la query.line_number, along with the object_id in the extended event can be used to investigate the query. È possibile utilizzare la query seguente per recuperare la definizione della routine.The following query can be used to retrieve the procedure definition. Il numero di riga può essere utilizzato per identificare la query all'interno della definizione:The line number can be used to identify the query within the definition:

select [definition] from sys.sql_modules where object_id=object_id  

Per altre informazioni sull'evento esteso sp_statement_completed , vedere l'articolo che spiega come recuperare l'istruzione che ha causato un evento.For more information about the sp_statement_completed extended event, see How to retrieve the statement that caused an event.

Utilizzo delle viste di gestione datiUsing Data Management Views

SQL ServerSQL Server supporta la raccolta delle statistiche di esecuzione per le stored procedure compilate in modo nativo, sia a livello di routine sia a livello di query. supports collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. La raccolta delle statistiche di esecuzione non è abilitata per impostazione predefinita a causa dell'impatto sulle prestazioni.Collecting execution statistics is not enabled by default due to performance impact.

Per abilitare e disabilitare la raccolta di statistiche sulle stored procedure compilate in modo nativo, usare sys.sp_xtp_control_proc_exec_stats (Transact-SQL).You can enable and disable statistics collection on natively compiled stored procedures using sys.sp_xtp_control_proc_exec_stats (Transact-SQL).

Quando la raccolta delle statistiche è abilitata con sys.sp_xtp_control_proc_exec_stats (Transact-SQL), è possibile usare sys.dm_exec_procedure_stats (Transact-SQL) per monitorare le prestazioni di una stored procedure compilata in modo nativo.When statistics collection is enabled with sys.sp_xtp_control_proc_exec_stats (Transact-SQL), you can use sys.dm_exec_procedure_stats (Transact-SQL) to monitor performance of a natively compiled stored procedure.

Quando la raccolta delle statistiche è abilitata con sys.sp_xtp_control_query_exec_stats (Transact-SQL), è possibile usare sys.dm_exec_query_stats (Transact-SQL) per monitorare le prestazioni di una stored procedure compilata in modo nativo.When statistics collection is enabled with sys.sp_xtp_control_query_exec_stats (Transact-SQL), you can use sys.dm_exec_query_stats (Transact-SQL) to monitor performance of a natively compiled stored procedure.

All'inizio dell'operazione di raccolta, abilitare la raccolta delle statistiche.At the start of collection, enable statistics collection. Eseguire quindi la stored procedure compilata in modo nativo.Then, execute the natively compiled stored procedure. Alla fine dell'operazione di raccolta, disabilitare la raccolta delle statistiche.At the end of collection, disable statistics collection. Analizzare quindi le statistiche di esecuzione restituite dalle viste a gestione dinamica.Then, analyze the execution statistics returned by the DMVs.

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).After you collect statistics, the execution statistics for natively compiled stored procedures can be queried for a procedure with sys.dm_exec_procedure_stats (Transact-SQL), and for queries with sys.dm_exec_query_stats (Transact-SQL).

Nota

Quando la raccolta delle statistiche è abilitata, per le stored procedure compilate in modo nativo il tempo del processo viene raccolto in millisecondi.For natively compiled stored procedures when statistics collection is enabled, worker time is collected in milliseconds. Se la query viene eseguita in meno di un millisecondo, il valore sarà 0.If the query executes in less than a millisecond, the value will be 0. Per le stored procedure compilate in modo nativo, il valore di total_worker_time non può essere accurato se più esecuzioni richiedono meno di 1 millisecondo.For natively compiled stored procedures, total_worker_time may not be accurate if many executions take less than 1 millisecond.

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:The following query returns the procedure names and execution statistics for natively compiled stored procedures in the current database, after statistics collection:

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.The following query returns the query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected, ordered by total worker time, in descending order:

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  st.dbid=db_id() and st.objectid in (select object_id   
from sys.sql_modules where uses_native_compilation=1)  
order by qs.total_worker_time desc  

Le stored procedure compilate in modo nativo supportano SHOWPLAN_XML (piano di esecuzione stimato).Natively compiled stored procedures support SHOWPLAN_XML (estimated execution plan). 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.The estimated execution plan can be used to inspect the query plan, to find any bad plan issues. I motivi comuni per i piani non validi sono:Common reasons for bad plans are:

  • Le statistiche non sono state aggiornate prima della creazione della routine.Stats were not updated before the procedure was created.

  • Indici mancantiMissing indexes

    Showplan XML viene ottenuto mediante l'esecuzione dell'istruzione Transact-SQLTransact-SQLseguente:Showplan XML is obtained by executing the following Transact-SQLTransact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO  

In alternativa, in SQL Server Management StudioSQL Server Management Studioselezionare il nome della routine e fare clic su Visualizza piano di esecuzione stimato.Alternatively, in SQL Server Management StudioSQL Server Management Studio, select the procedure name and click Display Estimated Execution Plan.

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.The estimated execution plan for natively compiled stored procedures shows the query operators and expressions for the queries in the procedure. SQL Server 2014SQL Server 2014 non supporta tutti gli attributi di SHOWPLAN_XML per le stored procedure compilate in modo nativo. does not support all SHOWPLAN_XML attributes for natively compiled stored procedures. Ad esempio, gli attributi correlati ai costi di Query Optimizer non fanno parte di SHOWPLAN_XML per la routine.For example, attributes related to query optimizer costing are not part of the SHOWPLAN_XML for the procedure.

Vedere ancheSee Also

Stored procedure compilate in modo nativoNatively Compiled Stored Procedures