Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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%'

 

image

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

image

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.

image


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

image

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.

image

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.

image

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

image 

and look at query stats DMV now..

image

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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • 5 Responses to “A-Z of In-Memory OLTP : Usage of sys.dm_exec_query_stats and sys.dm_exec_procedure_stats”

    1. Mohan said

      Thanks for sharing sir, its very useful.

    2. manu said

      I think it should be ON for both the cases. Will it be this way in final product too or do we need to enable it explicitly. Any harm in keeping it On by default? Forgot to thank you for sharing information on such new features. Cheers!

    3. […] https://sqlserver-help.com/2014/01/23/a-z-of-in-memory-oltp-usage-of-sys-dm_exec_query_stats-and-sys-… […]

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: