sys.dm_db_index_operational_stats – Day 5 – One DMV a Day

Hello Geeks and welcome to the Day 5 of the long series to come in 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.

As I have mentioned in yesterday’s blog, I will be explaining the most informative of all Index related DMVs. sys.dm_db_index_operational_stats is a DMV which is similar to sys.dm_db_index_usage_stats but give the details about operational stats than usage. To put it more clearly, the later tells you about how is your index used and sys.dm_db_index_operational_stats will tell you what operations are happening on the indexes. This is one of the most useful DMVs if you start using it regularly or collect this DMV data for troubleshooting performance related issues.

In this post I will try to show the most important columns in the DMV and how you can use it to interpret the information in the DMV. Similar to usage stats the stats in this DMV are also cumulative and are refreshed when your instance is restarted. So, as I always say, a DBA is as good as his information collected to troubleshoot or debug any issue.

To understand how to use this DMV I will first create a database, a table and a procedure to collect the output of sys.dm_db_index_operational_stats into a table. Later we will interpret the output of this table to derive to our results.

USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'Test_Operational_Stats')
	DROP DATABASE Test_Operational_Stats

CREATE DATABASE Test_Operational_Stats
GO

USE Test_Operational_Stats
GO

CREATE TABLE OperationStats_test(id INT PRIMARY KEY CLUSTERED, eName char(100))
GO
SELECT * FROM OperationStats_test
GO

CREATE PROCEDURE usp_collect_operational_stats (@DBName NVARCHAR(100), @ObjName NVARCHAR(100))
AS
BEGIN

IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE name = 'Operational_stats_tbl')
	SELECT	DB_NAME(database_id) AS DBName, OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName,
		leaf_insert_count, leaf_delete_count, leaf_update_count,
		nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count,
		row_lock_count, row_lock_wait_count, row_lock_wait_in_ms,
		page_lock_count, page_lock_wait_count, page_lock_wait_in_ms,
		page_latch_wait_count, page_latch_wait_in_ms,
		page_io_latch_wait_count, page_io_latch_wait_in_ms
	INTO Operational_stats_tbl
	FROM sys.dm_db_index_operational_stats(DB_ID('Test_Operational_Stats'), OBJECT_ID('OperationStats_test'), NULL, NULL) iop
	INNER JOIN sys.indexes i ON iop.index_id = i.index_id AND iop.object_id = i.object_id

ELSE
	INSERT INTO Operational_stats_tbl
	SELECT	DB_NAME(database_id) AS DBName, OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName,
		leaf_insert_count, leaf_delete_count, leaf_update_count,
		nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count,
		row_lock_count, row_lock_wait_count, row_lock_wait_in_ms,
		page_lock_count, page_lock_wait_count, page_lock_wait_in_ms,
		page_latch_wait_count, page_latch_wait_in_ms,
		page_io_latch_wait_count, page_io_latch_wait_in_ms
	FROM sys.dm_db_index_operational_stats(DB_ID('Test_Operational_Stats'), OBJECT_ID('OperationStats_test'), NULL, NULL) iop
	INNER JOIN sys.indexes i ON iop.index_id = i.index_id AND iop.object_id = i.object_id

END
GO

The result you will be interested are the counter values increase from first result set to the second and so on. The difference will give you the exact stats with the operation of inserts, updates and deletes that we are going to run in the below script. For the ease of reading I will put the output in a more readable format from the collected table.

--collect initial Operational stats
EXEC usp_collect_operational_stats 'Test_Operational_Stats', 'OperationStats_test'
CHECKPOINT
DBCC DROPCLEANBUFFERS()

--Do some Inserts into the table
DECLARE @i INT = 1
WHILE (@i <= 100000)
BEGIN
	INSERT INTO OperationStats_test 
	SELECT @i, 'Name' + CAST(@i AS NVARCHAR(6))
	SET @i = @i + 1
END

--Collect the Operational stats after inserts
EXEC usp_collect_operational_stats 'Test_Operational_Stats', 'OperationStats_test'
CHECKPOINT
DBCC DROPCLEANBUFFERS()

--Updates into the table
UPDATE OperationStats_test SET eName = 'bbbbb' + CAST(id AS NVARCHAR(6))
WHERE id <= 50000

--Collect the Operational stats after updates
EXEC usp_collect_operational_stats 'Test_Operational_Stats', 'OperationStats_test'
CHECKPOINT
DBCC DROPCLEANBUFFERS()

--Deletes on the table
DELETE OperationStats_test WHERE id > 75000

--Collect the Operational stats after deletes
EXEC usp_collect_operational_stats 'Test_Operational_Stats', 'OperationStats_test'

We have collected all the Operational stats after each operation from sys.dm_db_index_operational_stats into Operational_stats_tbl. As I cannot put the screenshot of the table due to high number of columns I have pull the data into excel and got the differences for each operation for all counters.

The results are pretty self-explanatory and you can also see the wait times on latch and lock waits which you can further use in troubleshooting performance issues. The leaf pages counts are changes happened due to the operation in the leaf pages which is the actual data. Similarly the non leaf counts is the changes happened to non leaf level pages which are the index pages.

So Now you know the importance of sys.dm_db_index_operational_stats and why to collect this data in definite intervals. Tomorrow I will be covering another DMV. So, 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