Download articleWe just added new database-scoped configuration options that will help with monitoring performance of natively compiled stored procedures. The new options XTP_PROCEDURE_EXECUTION_STATISTICS and XTP_QUERY_EXECUTION_STATISTICS are available now in Azure SQL Database, and will be available in the next major release of SQL Server. These options will improve your monitoring and troubleshooting experience for databases leveraging In-Memory OLTP with natively compiled stored procedures.
After enabling these options, you can monitor the performance of natively compiled stored procedures using Query Store, as well as the DMVs sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. Note that there is a performance impact to enabling execution statistics collection, thus we recommend to disable stats collection when not needed.
To enable execution statistics collection at the procedure level, run:
|
1 2 |
<span class="hljs-keyword"> ALTER</span><span> </span><span class="hljs-keyword">DATABASE</span><span> SCOPED CONFIGURATION </span><span class="hljs-keyword">SET</span><span> XTP_PROCEDURE_EXECUTION_STATISTICS = </span><span class="hljs-keyword">ON </span> |
To enable execution statistics collection at the query level, run:
|
1 |
<span class="hljs-keyword"> ALTER</span><span> </span><span class="hljs-keyword">DATABASE</span><span> SCOPED CONFIGURATION </span><span class="hljs-keyword">SET</span><span> XTP_QUERY_EXECUTION_STATISTICS = </span><span class="hljs-keyword">ON</span> |
The following example queries show the procedure-level and query-level execution statistics for natively compiled stored procedures:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
For more details about monitoring and troubleshooting the performance of natively compiled stored procedure, see Monitoring Performance of Natively Compiled Stored Procedures.
For an overview of In-Memory OLTP, including natively compiled stored procedures, see Overview and Usage Scenarios.
