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

QUESTO ARGOMENTO SI APPLICA A: sìSQL ServersìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Questo articolo illustra come monitorare le prestazioni di stored procedure compilate in modo nativo e di altri moduli T-SQL compilati in modo nativo.This article discusses how you can monitor the performance of natively compiled stored procedures and other natively compiled T-SQL modules.

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 specifica compilata in modo nativo.Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure. L'evento esteso viene generato dopo l'esecuzione di ogni query.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.

Uso di viste di Gestione dati e di Query StoreUsing Data Management Views and Query Store

SQL ServerSQL Server e Database SQLSQL Database 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. and Database SQLSQL Database support 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.

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.Execution statistics are reflected in the system views sys.dm_exec_procedure_stats and sys.dm_exec_query_stats, as well as in Query Store.

Abilitazione della raccolta delle statistiche di esecuzione a livello di routineEnabling Procedure-Level Execution Statistics Collection

SQL ServerSQL Server: abilitare o disabilitare 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). SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the procedure-level using 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:The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Database SQL di AzureAzure SQL Database: abilitare o disabilitare 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. Database SQL di AzureAzure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the database-scoped configuration option 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:The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON

Abilitazione della raccolta delle statistiche di esecuzione a livello di queryEnabling Query-Level Execution Statistics Collection

SQL ServerSQL Server: abilitare o disabilitare 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). SQL ServerSQL Server: Enable or disable statistics collection on natively compiled stored procedures at the query-level using 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:The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules on the current instance:

EXEC sys.sp_xtp_control_query_exec_stats 1

Database SQL di AzureAzure SQL Database: abilitare o disabilitare 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. Database SQL di AzureAzure SQL Database: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the database-scoped configuration option 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:The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON

Query di esempioSample Queries

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).

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  

Piani di esecuzione di queryQuery Execution Plans

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 2014 (12.x)SQL Server 2014 (12.x) 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