Monitoring Performance of Natively Compiled Stored Procedures

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

This topic discusses how you can monitor the performance of natively compiled stored procedures

Using Extended Events

Use the sp_statement_completed extended event to trace execution of a 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. The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. A natively compiled stored procedure that uses a lot of CPU time may have performance problems.

line_number, along with the object_id in the extended event can be used to investigate the query. The following query can be used to retrieve the procedure definition. The line number can be used to identify the query within the definition:

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

For more information about the sp_statement_completed extended event, see How to retrieve the statement that caused an event.

Using Data Management Views

SQL Server supports collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. Collecting execution statistics is not enabled by default due to performance impact.

You can enable and disable statistics collection on natively compiled stored procedures using sys.sp_xtp_control_proc_exec_stats (Transact-SQL).

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.

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.

At the start of collection, enable statistics collection. Then, execute the natively compiled stored procedure. At the end of collection, disable statistics collection. Then, analyze the execution statistics returned by the DMVs.

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

Note

For natively compiled stored procedures when statistics collection is enabled, worker time is collected in milliseconds. If the query executes in less than a millisecond, the value will be 0. For natively compiled stored procedures, total_worker_time may not be accurate if many executions take less than 1 millisecond.

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  

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  

Natively compiled stored procedures support SHOWPLAN_XML (estimated execution plan). The estimated execution plan can be used to inspect the query plan, to find any bad plan issues. Common reasons for bad plans are:

  • Stats were not updated before the procedure was created.

  • Missing indexes

    Showplan XML is obtained by executing the following Transact-SQL:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO  

Alternatively, in SQL Server Management Studio, select the procedure name and click Display Estimated Execution Plan.

The estimated execution plan for natively compiled stored procedures shows the query operators and expressions for the queries in the procedure. SQL Server 2014 does not support all SHOWPLAN_XML attributes for natively compiled stored procedures. For example, attributes related to query optimizer costing are not part of the SHOWPLAN_XML for the procedure.

See Also

Natively Compiled Stored Procedures