Have you ever played any game where crime has occurred in past and you need to look at evidences to find out who was criminal? Any post-mortem needs data collected during past. In SQL Server world DBAs are detectives where they sometime needs post-mortem about performance issue reported on server. To equip them SQL Server provides the statistics about the query and being is constantly gathered once they are executed. As long as plan is there is cache, we can use stats DMVs (sys.dm_exec_query_stats and sys.dm_exec_procedure_stats) to find the time taken by query and resources taken in the past. If you try to find query to find Top 10 CPU / IO consumers in the past, you will easily get query.

There is a little difference in this behavior when it comes to In-Memory OLTP. The gathering of execution stats collection is off by default. Here is the quick repro about what I mean. I have created two procedures to do same task.

One is interoperable.

Create Procedure [dbo].[SelectName_traditional]
@iID int

as Select vFName,vLName from dbo.MyFirstMemporyOptimizedTable
  where iID = @iID
GO

and another is natively compiled

CREATE PROCEDURE [dbo].[SelectName_Native] @iID INT
  WITH 
    NATIVE_COMPILATION,  SCHEMABINDING,  EXECUTE AS OWNER
AS 
BEGIN ATOMIC 
  WITH 
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')  Select vFName,vLName from dbo.MyFirstMemporyOptimizedTable
  where iID = @iID

END
 

Let’s execute both stored procedure 10 times using below code

exec SelectName_traditional 1
go 10
exec SelectName_Native 1
go 10

If I look at stats DMV, I would see information only for “SelectName_traditional” procedure as below. Here is the query for query_stats DMV

SELECT 
QT.TEXT AS STATEMENT_TEXT,
QP.QUERY_PLAN,
QS.TOTAL_WORKER_TIME AS CPU_TIME
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
where QT.TEXT like '%MyFirstMemporyOptimizedTable%'

Here is the query for procedure_stats DMV

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('SQLSeverHelp_IMO') 

As we can see above that there is no entry for SelectName_Native.

Above confirms that, by default collection is off as its recommended to turn on stats collection only for a limited amount of time as needed for troubleshooting. This is done to minimize the performance impact on the production workload due to stats collection. We can turn on (and turn off as well) the collection of statistics by running below “control” procedures. One procedure is for collection at query level (within natively complied procedure) and other is at procedure level.

sys.sp_xtp_control_query_exec_stats: Here is the tooltip shown in SSMS.

sys.sp_xtp_control_proc_exec_stats:Here is the tooltip shown in SSMS.

As you would notice via tool tip that “query” stats can be turned on for a specific database id and object id, which gives us granular control of data collection. Once they are turned on, we can see the entry in statistics DMVs.

I have done DBCC FREEPROCCACHE and ran the same batch again to execute both procedure 10 times. Here is the result of earlier query for procedure stats DMV.

Now we can see two rows. Notice Zero values in various columns. Let’s do the same for query level.

and look at query stats DMV now..

Now, we can see new row for natively complied procedure as well.

Few important notes, based on my learning on CTP2.

1. the changes done via control procedures are reverted back to OFF after restart.
2. DBCC FREEPROCCACHE doesn’t impact the entry shown for natively complied procedures in stats DMV as they are not in procedure cache.
3. CPU, IO column value would be zero for natively compiled procedures.
4. Turning off the control procedures doesn’t remove the value. Only way to remove them is either drop and recreate the procedure or restart.

Hope you have learned something new today.