sys.dm_exec_query_stats – Day 36 – One DMV a Day

Hello Geeks and welcome to the Day 36 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

In the last post I have written on how to use sys.dm_exec_procedure_stats. This covers how to get procedure stats. The DMV I am going to talk about today gives you more granular information. Sys.dm_exec_query_stats gives the stats of statements executed inside the procedures. It also includes individual ad hoc statements. The details are saved in sys.dm_exec_query_stats till the plan exists in the cache.

I will edit the procedure I used yesterday. I will just add two select statements instead of one.

CREATE PROCEDURE usp_getPubLogger_prc
AS BEGIN
	SELECT * FROM pubLogger_tbl
	SELECT * FROM pubtrans_tbl
END

To collect the stats let me follow the same steps as I did for sys.dm_exec_procedure_stats. The collection table will hold all necessary information form sys.dm_exec_query_stats. We can use this when needed for troubleshooting.

Create collection table:

CREATE TABLE queryExecStats_tbl
	(sqlHandle varbinary(64)
	,startOffset INT
	,endOffset INT
	,sqlStmnt NVARCHAR(MAX)
	,planHandle VARBINARY(64)
	,Exec_Cnt BIGINT
	,Exec_Time BIGINT
	,Disk_Reads BIGINT
	,Mem_Reads BIGINT
	,Total_Writes BIGINT
	,CLR_Time BIGINT
	,Total_Time BIGINT
	,Total_Rows BIGINT
	,SampledOn DATETIME)
GO

CREATE NONCLUSTERED INDEX IX_queryExecStats_SampledOn ON queryExecStats_tbl(SampledOn)
GO
CREATE NONCLUSTERED INDEX IX_queryExecStats_sqlHandle ON queryExecStats_tbl(sqlHandle)
GO

Create the procedure to collect stats. This should be run at regular frequency to collect data from sys.dm_exec_query_stats.

CREATE PROCEDURE usp_collectExecQueryStats_prc
AS
BEGIN
	INSERT INTO queryExecStats_tbl 
		(sqlHandle
		,startOffset
		,endOffset
		,planHandle
		,Exec_Cnt 
		,Exec_Time 
		,Disk_Reads
		,Mem_Reads 
		,Total_Writes
		,CLR_Time
		,Total_Time
		,Total_Rows
		,SampledOn)
	SELECT sql_handle,
		statement_start_offset,
		statement_end_offset,
		plan_handle,
		execution_count AS Exec_Cnt,
		total_worker_time AS Exec_Time,
		total_physical_reads AS Disk_Reads,
		total_logical_reads AS Mem_Reads,
		total_logical_writes AS Total_Writes,
		total_clr_time AS CLR_Time,
		total_elapsed_time AS Total_Time,
		total_rows AS Total_Rows,
		GETDATE()
	FROM sys.dm_exec_query_stats

	UPDATE qes
		SET sqlStmnt = SUBSTRING(est.text, (qes.startOffset/2)+1, 
        ((CASE qes.endOffset
          WHEN -1 THEN DATALENGTH(est.text)
         ELSE qes.endOffset
         END - qes.startOffset)/2) + 1)
	FROM queryExecStats_tbl qes
	CROSS APPLY sys.dm_exec_sql_text(sqlHandle) est
	WHERE sqlStmnt IS NULL
END
GO

Now let us run the procedure couple of times. We will collect the stats in between from sys.dm_exec_query_stats.

EXEC usp_collectExecQueryStats_prc
GO
EXEC usp_getPubLogger_prc
GO
EXEC usp_collectExecQueryStats_prc
GO
EXEC usp_getPubLogger_prc
GO
EXEC usp_collectExecQueryStats_prc
GO
DBCC DROPCLEANBUFFERS()
GO
EXEC usp_getPubLogger_prc
GO
EXEC usp_collectExecQueryStats_prc
GO

To check the stats run the below query on collection table which has data collected from sys.dm_exec_query_stats. To get to see what we are interested in I filtered on sqlHandle for the procedure I ran.

;WITH c AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY sqlHandle,startOffset,endOffset,SampledOn) As RowNum,
		sqlStmnt,
		Exec_Cnt,
		Exec_Time,
		Disk_Reads,
		Mem_Reads,
		Total_Writes,
		CLR_Time,
		Total_Time,
		Total_Rows,
		SampledOn
	FROM queryExecStats_tbl
	WHERE sqlHandle = 0x03000600FDA84C3AF6428900CCA3000001000000000000000000000000000000000000000000000000000000
)
SELECT c1.sqlStmnt, 
	c1.Exec_Cnt - c2.Exec_Cnt AS Execs,
	(c1.Exec_Time - c2.Exec_Time) AS Total_Exec_Time,
	(c1.Total_Time - c2.Total_Time)AS Total_Total_Time,
	c1.Disk_Reads - c2.Disk_Reads AS Physical_Reads,
	c1.Mem_Reads - c2.Mem_Reads AS Logical_Reads,
	c1.Total_Writes - c2.Total_Writes AS Total_Writes,
	c1.Total_Rows - c2.Total_Rows AS Total_Rows,
	c1.CLR_Time - c2.CLR_Time AS Total_CLR_Time,
	c1.SampledOn
FROM c c1
INNER JOIN c c2
ON c1.RowNum = c2.RowNum + 1
AND c1.sqlStmnt = c2.sqlStmnt

This output shows stats for individual statements in my procedure. I can also get the plan_handle and see the individual statements plan using sys.dm_exec_text_query_plan. You can do more than you think you can with DMVs. So, continue to follow my series. Stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook