Enabling the collection of Info. for natively compiled stored procedures
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 and elapsed_time, do not get collected by DMVs such as sys.dm_exec_query_stats and sys.dm_exec_procedure_stats. In fact, these DMVs include no information about natively compiled stored procedures.
Instead, you need to specifically enable the collection of execution statistics by using one of the following system stored procedures:
sys.sp_xtp_control_proc_exec_stats Use this system stored procedure to enable statistics collection for your SQL Server instance at the procedure level.
sys.sp_xtp_control_query_exec_stats Use this system stored procedure to enable statistics collection at the query level for selected natively compiled stored procedures.
After enabling statistics collections at the procedure level, you can query the sys.dm_exec_procedure_stats DMV to review the results. You can also review the statistics collection at the query level by executing a queryagainst the sys.dm_exec_query_stats DMV.
The information available in the query-level results is similar to the procedure-level statistics, but includes a row for each statement in the natively compiled stored procedure and includes the query text for each statement. Note that total_worker_time and total_elapsed_time were excluded from this example to restrict the width of the query results.
Keep in mind that enabling the collection of execution statistics can have an adverse effect on the performance of natively compiled stored procedures. Rather than collect statistics globally for an instance, you should collect statistics for selected natively compiled stored procedures only to reduce this impact.
Example:
Answer:
