A-Z of In-Memory OLTP : Usage of sys.dm_exec_query_stats and sys.dm_exec_procedure_stats
Posted by blakhani on January 23, 2014
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.
- the changes done via control procedures are reverted back to OFF after restart.
- DBCC FREEPROCCACHE doesn’t impact the entry shown for natively complied procedures in stats DMV as they are not in procedure cache.
- CPU, IO column value would be zero for natively compiled procedures.
- 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.