Collection of execution statistics for natively compiled stored procedures
Use the system stored procedures sys.sp_xtp_control_proc_exec_stats and sys.sp_xtp_control_query_exec_stats to enable or disable the collection of execution statistics for natively compiled stored procedures at the procedure level or query level, respectively.
After enabling statistics collection, use the sys.dm_exec_procedure_stats and sys.dm_exec_query_stats DMVs to review the statistics.
(Note: The goal of using memory-optimized tables is to execute processes as quickly as possible.Consequently, you could be surprised that some statistics, such as worker_time andelapsed_time, do not get collected by DMVs such as sys.dm_exec_query_stats andsys.dm_exec_procedure_stats. In fact, these DMVs include no information about nativelycompiled stored procedures.)
After enabling statistics collections at the procedure level, you can query the sys.dm_exec_procedure_stats DMV to review the results.
SELECT OBJECT_NAME(PS.object_id) AS obj_name,
cached_time as cached_tm, last_execution_time as last_exec_tm, execution_count as ex_cnt, total_worker_time as wrkr_tm, total_elapsed_time as elpsd_tm FROM sys.dm_exec_procedure_stats PS INNER JOIN sys.all_sql_modules SM ON SM.object_id = PS.object_id
WHERE SM.uses_native_compilation = 1;
sys.dm_exec_query_stats In combination with sys.dm_exec_sql_text, find queries that use a lot of resources, such as CPU time or IO.
To monitor SQL Database performance, you can monitor specific metrics in the Azure portal. In addition, you can use the following DMVs with SQL Database for monitoring: sys.database_connection_stats, sys.dm_db_resource_stats, sys.dm_exec_query_stats, sys.dm_tran_locks, and sys.event_log. Extended Events are also available for SQL Database, although the set of supported events is smaller than the set that supports SQL Server.
You can also review the statistics collection at the query level by executing a query against the sys.dm_exec_query_stats DMV
SELECT st.objectid as obj_id, OBJECT_NAME(st.objectid) AS obj_nm, SUBSTRING(st.text,(QS.statement_start_offset / 2 ) + 1, ((QS.statement_end_offset - QS.statement_start_offset)/2)
+ 1) AS 'Query', QS.last_execution_time as last_exec_tm, QS.execution_count as ex_cnt FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(sql_handle) st INNER JOIN sys.all_sql_modules SM ON SM.object_id = st.objectid WHERE SM.uses_native_compilation = 1
Example:
SELECT t.objectId, objectID_name(t.objectID) as 'object name', t.text as 'query text', q.execution_count, q.total_worker_time FROM q CROSS APPLY sys.dm_exec_sql_text(sql_handle) t WHERE t.dbid = db_id() and t.objectID in ( SELECT object_id from sys.sql_modules where uses_native_complilation = ORDER BY q.total_worker_time desc
Answer:
