In-Memory OLTP Programmability - Pro SQL Server Internals - page 683

■ Note Do not collect execution statistics unless you are troubleshooting performance.

Listing 33-5 shows the code that returns execution statistics for stored procedures using the sys.dm_exec_
procedure_stats view.

Listing 33-5. Analyzing stored procedures execution statistics

select
object_name(object_id) as [Proc Name]
,execution_count as [Exec Cnt]
,total_worker_time as [Total CPU]
,convert(int,total_worker_time / 1000 / execution_count)
as [Avg CPU] -- in Milliseconds
,total_elapsed_time as [Total Elps]
,convert(int,total_elapsed_time / 1000 / execution_count)
as [Avg Elps] -- in Milliseconds
,cached_time as [Cached]
,last_execution_time as [Last Exec]
,sql_handle
,plan_handle
,total_logical_reads as [Reads]
,total_logical_writes as [Writes]
from
sys.dm_exec_procedure_stats
order by
[AVG CPU] desc

Figure 33-4 illustrates the output of the code from Listing 33-5. As you can see, neither the sql_handle nor
plan_handle columns are populated. Execution plans for natively-compiled stored procedures are embedded into
the code and not cached in plan cache. Nor are I/O related statistics provided. Natively-compiled stored procedures
work with memory-optimized tables only, and therefore there is no I/O involved.

Figure 33-4. Data from sys.dm_exec_procedure_stats view

Listing 33-6 shows the code that obtains execution statistics for individual statements using the
sys.dm_exec_query_stats view.

Listing 33-6. Analyzing stored procedure statement execution statistics

select
substring(qt.text, (qs.statement_start_offset/2)+1,
((
case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2)+1) as SQL