Last week, I mentioned in my TSQL2sDay index summary post, that I’d be writing a few posts on the information that is contained in sys.dm_db_index_operational_stats. The posts will be the following:

• Index Black Ops Part 1 – Locking and Blocking
• Index Black Ops Part 2 – Page IO Latch, Page Latch
• Index Black Ops Part 3 – Index Usage
• Index Black Ops Part 4 – Index Overhead and Maintenance
• Index Black Ops Part 5 – Page Splits
• Index Black Ops Part 6 – Fill Factor vs. Page Splits

We’re up to part three now and according to the schedule, this should have been published last Thursday.  Those that were at SQL Saturday #50 heard about the laptop issues that I had and I’m going to just blame that for the delay.  But now that I am back in action, on to part 3.  For the current post, we are going to look at the index usage data that is contained in sys.dm_db_index_operational_stats.

Index Usage

The goal today is to find out how much use an index is providing to your database through DMVs.  This information can be used to provide valuable insight into the value of the index.  It’s an easy calculation, if the index is used a lot more than other indexes on the table.  Then it’s probably a “better” index to have on the system.

There are a number of columns that we’ll look at when it comes to reviewing index usage:

• range_scan_count (bigint) – Cumulative count of range and table scans started on the index or heap.
• singleton_lookup_count (bigint) – Cumulative count of single row retrievals from the index or heap.
• leaf_insert_count (bigint) – Cumulative count of leaf-level inserts.
• leaf_delete_count (bigint) – Cumulative count of leaf-level deletes.
• leaf_update_count (bigint) – Cumulative count of leaf-level updates.
• leaf_ghost_count (bigint) – Cumulative count of leaf-level rows that are marked as deleted, but not yet removed. These rows are removed by a cleanup thread at set intervals.
• nonleaf_insert_count (bigint) – Cumulative count of inserts above the leaf level.
• nonleaf_delete_count (bigint) – Cumulative count of deletes above the leaf level.
• nonleaf_update_count (bigint) - Cumulative count of updates above the leaf level.

Range Scan

The first column that we’ll look at is range_scan_count.  As noted above, this column collects a count of seek and scan operations that occur against an index.  To illustrate execute the following query:

USE AdventureWorks
GO
SELECT TOP 100 *
FROM Person.Contact
GO
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,ios.range_scan_count
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY ios.range_scan_count DESC

The results for the second query should look like the following:

As you can see, for the 100 rows returned from query against Person.Contact there is an increment for 1 for the index that was used in sys.dm_db_index_operational_stats.

Singleton Lookup

The next column is to look at is the singleton_lookup_count.  This column provides a count of the number of rows retrieved by a RowID and Key lookups.  To demonstrate how this is accumulated, create the index below and run the accompanying queries.  Turn on the Actual Execution Plan option for the query to view the execution included below as well.

USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Contact_FirstName')
DROP INDEX IX_Contact_FirstName ON Person.Contact
GO
CREATE INDEX IX_Contact_FirstName ON Person.Contact(FirstName)
GO
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE FirstName = 'Adam'
GO
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,ios.range_scan_count
,ios.singleton_lookup_count
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY ios.range_scan_count DESC
GO

If your Adventureworks database is similar to mine, then the execution plan for the SELECT statement on Person.Contact will look like the following:

The results of the SELECT will return 53 rows where first name is “Adam”, not shown.  The second result set for the DMV query will return results as below:

In this case, the range_scan_count incremented by 1 for the index seek operation on IX_Contact_FirstName.  The count for singleton_lookup_count increased by 53 for the lookups against PK_Contact_ContactID.

While this appears to be a count of rows returned from by the Key Lookup operation, it is actually a count of the number of operations that occurred against the index.

To help show this, run the following set of queries:

USE AdventureWorks
GO
WITH cContact
AS (
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE FirstName = 'Adam'
)
SELECT FirstName, LastName, EmailAddress
FROM cContact
WHERE LastName = 'Reynolds'
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,ios.range_scan_count
,ios.singleton_lookup_count
FROM sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY ios.range_scan_count DESC
GO

Through the use of the CTE, the SELECT results are reduced from 53 rows to a single row being returned.  Operationally, though, the lookup executed 53 times before the final results were filtered to where last name equals “Reynolds”.

After this execution, the singleton_lookup_count increased again by 53 instead of by 1 for the number of lookups that were returned by the query.  This means that the results of the query against sys.dm_db_index_operational_stats are for the number of operations, not the number of rows returned by the final result set.

Index Modifications

Up to this point, we’ve investigate the columns that return results based on the number of operations that have occurred due to SELECT statements.  Now the focus will be shifted to DML operations.  For these operations, we’ll look at the columns leaf_insert_count, leaf_update_count, leaf_delete_count, and leaf_ghost_count.

The following script will help demonstrate how DML operations will accumulate in the DMV:

USE AdventureWorks
GO
IF OBJECT_ID('dbo.KungFu') IS NOT NULL
DROP TABLE dbo.KungFu
GO
CREATE TABLE dbo.KungFu
(
KungFuID int
,Hustle  bit
,CONSTRAINT PK_KungFu_KungFuID PRIMARY KEY CLUSTERED  (KungFuID)
)
GO
INSERT INTO dbo.KungFu
SELECT ROW_NUMBER() OVER (ORDER BY t.object_id),  t.object_id % 2
FROM sys.tables t
GO
DELETE FROM dbo.KungFu
WHERE Hustle = 0
GO
UPDATE dbo.KungFu
SET Hustle = 0
WHERE Hustle = 1
GO
SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id)  as table_name
,i.name as index_name
,ios.leaf_insert_count
,ios.leaf_update_count
,ios.leaf_delete_count
,ios.leaf_ghost_count
FROM  sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
INNER JOIN  sys.indexes i ON i.object_id = ios.object_id AND i.index_id =  ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER  BY ios.range_scan_count DESC

Here’s a basic walk through the script:

1. Create a table name KungFu
2. Add 73 rows to KungFu
3. Remove 23 rows from KungFu
4. Update 50 rows in KungFu

The results of the sys.dm_db_index_operational_stats query should look like the following:

A couple obvious items in the results are the inserts and updates against the index.  Leaf_insert_count and leaf_update_count contain this information, respectively.

The next couple columns contain the delete row information.  The first column contains the leaf pages that have been deleted and those that are scheduled for deletion but are still part of the index tree.

The non-leaf columns listed at the beginning haven’t been discussed in this post.  The columns tabulate information almost exactly as the columns for the leaf columns except that they are for the non-leaf pages of the index.

What About sys.dm_db_index_usage_stats?

The first thing that may have crossed your mind with the topic of index usage may have been the other DMV that is often used – namely sys.dm_db_index_usage_stats.  This DMV is all well and good but it doesn’t provide the full picture of the impact that plans are having on an index.

As you may know, sys.dm_db_index_usage_stats returns results based on the number of plans that have been executed that will utilize an index.  The stark difference with sys.dm_db_index_operational_stats is that it returns the number of operations that have actually occurred in those same plans.

Summary

Hopefully this information has been helpful in providing some insight into how this DMV works.  Please feel free to leave comments on this DMV below.

Related posts:

1. Index Black Ops Part 1 – Locks and Blocking
2. Index Black Ops Part 2 – Page IO Latch, Page Latch
3. Snapshots of Index Contention