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.

stat\Operation SELECT INSERT UPDATE DELETE
leaf_insert_count 0 100000 0 0
leaf_delete_count 0 0 0 18574
leaf_update_count 0 0 50000 0
nonleaf_insert_count 0 1409 0 0
nonleaf_delete_count 0 0 0 266
nonleaf_update_count 0 0 0 0
row_lock_count 0 202816 6162 6161
row_lock_wait_count 0 0 0 0
row_lock_wait_in_ms 0 0 0 0
page_lock_count 0 101408 87 88
page_lock_wait_count 0 0 0 0
page_lock_wait_in_ms 0 0 0 0
page_latch_wait_count 0 3 0 0
page_latch_wait_in_ms 0 4 0 0
page_io_latch_wait_count 0 198 6 4
page_io_latch_wait_in_ms 0 218 140 51

 

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

2 Comments on “sys.dm_db_index_operational_stats – Day 5 – One DMV a Day”

  1. Nice series Manohar.

    Because you are joining to sysindexes, this example doesn’t work unless you unless you are connected to the database you are reviewing, so I’m not sure I get the point of having the database name as a parameter.

    Secondly, you’re using the sysindexes view, but as per this page http://msdn.microsoft.com/en-us/library/ms190283.aspx that’s been marked for replacement.

    Keep up the good work!

    1. Thanks for the inputs Toby. Yes, the database name is not relevant as the procedure is created in the database. I have changed the sysindexes to sys.indexes. 🙂

Leave a Reply to Toby Cancel reply

Your email address will not be published. Required fields are marked *