Introduction to the Index Operational Statistics Dynamic Management Function

Microsoft SQL Server offers a variety of methods for accessing internal metadata: system compatibility views, system views, INFORMATION_SCHEMA views, Profiler, extended events, Dynamic Management Objects, and the list goes on…

My favorite of these happens to be the Dynamic Management Objects and I’ve written a book and countless articles on the subject over the years because it seems as though I keep finding interesting ways to use these internal constructs all the time. Over the course of the next few articles I’m going to take you on a tour of one of the most versatile of these objects: sys.dm_db_index_operational_stats.

Index Access Patterns, Locking, Latches, and Compression

sys.dm_db_index_operational_stats provides information about many aspects focused around how your indexes and heaps (tables with no clustered index) are being accessed by calls made from end users of your databases. It offers us information around:

From this single system function you’re able to make valuable decisions regarding proper settings for fill factor (the fullness at which your index leaf pages are filled by default) and pin-pointing indexes incurring high levels of latches and locks should analysis of wait statistics show you’re seeing those types of waits as your most cumbersome impediments to performance.  This Dynamic Management Function (DMF) also dovetails with the other indexing Dynamic Management Objects (DMOs) as well so you can tell multiple stories about the lives of your SQL Server indexes.  As you’ll see through the bulk of this first article in the series it adds a depth of detail to the information provided by sys.dm_db_index_usage_stats, a Dynamic Management View (DMV) I’ve gone into detail in SQL Server Pro as well as my book on Dynamic Management Objects. 

Syntax

Don’t be confused by the name “Dynamic Management Function”. These objects are like any other SQL Server function. You call for the results through a SELECT statement, passing in one or more parameters. The results are returned back in the form of a table-valued result set: one or more rows with multiple columns per row.  From the previous paragraph you can see that you should expect a very wide result set if you query all columns. For the sake of this article I’ll forgo returning all columns and instead focus on those that are only important for the subject of this series.  Should you want to see a complete listing of columns and a very high-level explanation of what they cover you should visit Microsoft’s official documentation for sys.dm_db_index_operational_stats.

The syntax for calling sys.dm_db_index_operational_stats is:

SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), , , );

If you’re not familiar with the concept of Template Parameters like I used above please consult this article.  The tldr; (too-long-didn’t read) on this is to use the Ctl+Shift+M keyboard shortcut in SQL Server Management Studio (SSMS) when you see the syntax of to replace those placeholders with values for your specific needs.

If left unadulterated that command will provide you with the results covering all objects (indexes and heaps) and any associated indexes without regards to limiting to a specific partition. Of course by doing so you get a great amount of information returned but it has little value because you have no context for the results. This is why I always end up joining the indexing DMOs to other system views that provide that context for the results (as well as filtering down the rows that are returned as well as the columns I care to see.  For that contextual requirement those system views are:

sys.objects – while I could use the OBJECT_NAME(object_id) system function to return the name of the table or view associated with the object_id exposed by sys.dm_db_index_operational_stats I also need to filter the results because I’m only interested in user objects – not those system tables and views that are used internally by SQL Server. To do so I need access to the is_ms_shipped column in sys.objects. While I’m there I may as well return the object name (name column) and type of object (type_desc).

That leaves us with a base structure of:

SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), , , )INNER JOIN sys.indexes I ON ixO.object_id = I.object_id AND ixO.index_id = I.index_idINNER JOIN sys.objects AS sOON sO.object_id = ixO.object_idWHERE sO.is_ms_shipped = 0;

It’s from this singular foundation that the queries we employ against sys.dm_db_index_operational_stats will be built.  What follows would be the “broad brush” approach to returning the complete set of columnar results before we move on towards using sys.dm_db_index_operational_stats as a tool for both reactive performance analysis and optimizing schemas proactively for performance:

SELECT  --IDENTIFICATION:DB_NAME(ixO.database_id) AS database__name,O.name AS object__name,I.name AS index__name,I.type_desc AS index__type,    ixO.index_id ,    ixO.partition_number ,--LEAF LEVEL ACTIVITY:    ixO.leaf_insert_count ,    ixO.leaf_delete_count ,    ixO.leaf_update_count ,ixO.leaf_page_merge_count ,    ixO.leaf_ghost_count ,--NON-LEAF LEVEL ACTIVITY:    ixO.nonleaf_insert_count ,    ixO.nonleaf_delete_count ,    ixO.nonleaf_update_count ,    ixO.nonleaf_page_merge_count ,--PAGE SPLIT COUNTS:    ixO.leaf_allocation_count ,    ixO.nonleaf_allocation_count ,--ACCESS ACTIVITY:    ixO.range_scan_count ,    ixO.singleton_lookup_count ,    ixO.forwarded_fetch_count ,--LOCKING ACTIVITY:    ixO.row_lock_count ,    ixO.row_lock_wait_count ,    ixO.row_lock_wait_in_ms ,    ixO.page_lock_count ,    ixO.page_lock_wait_count ,    ixO.page_lock_wait_in_ms ,    ixO.index_lock_promotion_attempt_count ,    ixO.index_lock_promotion_count ,--LATCHING ACTIVITY:    ixO.page_latch_wait_count ,    ixO.page_latch_wait_in_ms ,    ixO.page_io_latch_wait_count ,    ixO.page_io_latch_wait_in_ms ,    ixO.tree_page_latch_wait_count ,    ixO.tree_page_latch_wait_in_ms ,    ixO.tree_page_io_latch_wait_count ,    ixO.tree_page_io_latch_wait_in_ms ,--COMPRESSION ACTIVITY:    ixO.page_compression_attempt_count ,    ixO.page_compression_success_count FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ixOINNER JOIN sys.indexes I ON ixO.object_id = I.object_id AND ixO.index_id = I.index_idINNER JOIN sys.objects AS OON O.object_id = ixO.object_idWHERE O.is_ms_shipped = 0;

(Note that going forward in this article I’ve already swapped-out the template parameters.)

Obviously we’re not going to be returning all columns from these system objects as we proceed. As we navigate this series you’ll definitely see me shedding the SELECT-star methodology to focus on just those columns that help to tell the story I want to share.

The Goal of this First Article

In this first article I want to explain how you can translate queries and execution plans into the metadata you collect from sys.dm_db_index_operational_stats. Considering I’ve spent time taking you through the companion DMV sys.dm_db_index_usage_stats I’m also going to compare and contrast that activity as it’s persisted there too. In subsequent articles we will dig into how you can use sys.dm_db_index_operational_stats to diagnose locking and latching waits, building cases for tweaking fill factors, looking into page lock escalation and even targeting candidates for page compression. First it’s important to understand what operations contribute to the metrics in this DMF however.

In order to do so I’m going to have you create a very small test database for most of this effort if you’d like to follow along with fingers to the keyboard.  We will use this small sample database throughout this series and likely others.

Create the Test Environment

To play on the theme of my SQL Cruise events, for the sake of this article and ones to follow in this series please create a small test database called “lifeboat” in SIMPLE recovery (100mb in size should suffice.)  Then run the following query to create a small table with 8 rows and a clustered a non-clustered index:

USE [lifeboat]GOCREATE TABLE [dbo].[DatabaseTypes]([DBTypeID] [smallint] NOT NULL,[DBType] [varchar](20) NOT NULL);ALTER TABLE [dbo].[DatabaseTypes] ADD  CONSTRAINT [PK_DatabaseTypes] PRIMARY KEY CLUSTERED ([DBType] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90);CREATE UNIQUE NONCLUSTERED INDEX [NC_DatabaseTypes_DBTypeID] ON [dbo].[DatabaseTypes]([DBTypeID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100);

At this point you should be able to run the following query and see there are no results returned. This means that there is yet to be any activity in the lifeboat database and one of the reasons I had you create a new database if you wanted to play along at home. You may not have the luxury of restarting SQL Services as I do in order to simulate a clean set of metrics.

SELECT *FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) AS ixOINNER JOIN sys.indexes I ON ixO.object_id = I.object_id AND ixO.index_id = I.index_idINNER JOIN sys.objects AS OON O.object_id = ixO.object_idWHERE O.is_ms_shipped = 0;

Contrasting  sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats

It’s finally time to look at that clean slate of a database and a few sample queries to run to show what the underlying impact is for metadata recorded in sys.dm_db_index_operational_stats.

When executing these queries please ensure you’ve selected to show the actual execution plan.

For these series of tests we’re interested in results served through the following columns in sys.dm_db_index_operational_stats:

Regarding the sys.dm_db_index_usage_stats DMV we’re going to focus on the following columns:

Query 1: Index Updates

I want to kick off these comparative behaviors by looking at the dark side of indexing: the negative impact due to maintaining additions of new rows through INSERT operations and maintaining changes in column values due to UPDATES and DELETES.  We have this empty table and it’s time to add some values to it. If you run the command below:

INSERT INTO lifeboat.[dbo].[DatabaseTypes] (DBTypeID, DBType)VALUES (1, 'System'), (2, 'User'), (3, 'Sample'), (4, 'Archive'), (5, 'Test'), (6, 'Stage'), (7, 'QA'), (8, 'Dev');

The execution plan should look like this:

We should now see the effects of those 8 values being inserted into the table in both DMOs:

SELECT  --IDENTIFICATION:DB_NAME(ixO.database_id) AS database__name,O.name AS object__name,I.name AS index__name,I.type_desc AS index__type,--LEAF LEVEL ACTIVITY:    ixO.leaf_insert_count ,--LOCKING ACTIVITY:    ixO.row_lock_count ,    ixO.page_lock_count ,--LATCHING ACTIVITY:    ixO.page_io_latch_wait_countFROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ixOINNER JOIN sys.indexes I ON ixO.object_id = I.object_id AND ixO.index_id = I.index_idINNER JOIN sys.objects AS OON O.object_id = ixO.object_idWHERE O.is_ms_shipped = 0;

The eight individual inserts are recorded as are the associated row and page locks and an ancillary latch. The associated page_io_latch_wait_in_ms value was 0 meaning the latch wait was sub-millisecond in length and therefore statistically insignificant. What you can take away from this is the overhead of indexing on your SQL databases. Every index you add for sake of improving read performance has a negative impact when it comes to writes. Every write needs to be recorded across all applicable indexes.

Those of you who have also read my series of articles on the sys.dm_db_index_usage_stats (aka ixU) Dynamic Management View (DMV) may wonder how sys.dm_db_index_operational_stats (aka ixO)  differs in comparison since we’re recording “usage” statistics here as well.  While  they both collect usage information consider that the operational DMF (ixO) provides metrics from the perspective of the index B-tree level whereas the usage DMV (ixU) presents information from the perspective of the index itself.  Those eight rows inserted above and recorded as eight individual leaf inserts are represented by a single index write operation from the standpoint of sys.dm_db_index_usage_stats:

SELECT O.name AS object__name,I.name AS index__name,I.type_desc AS index__type,ixU.user_seeks + ixU.user_scans + ixU.user_lookups AS total_user_reads,ixU.user_updates AS total_user_writesFROM sys.dm_db_index_usage_stats AS ixUINNER JOIN sys.indexes AS I ON ixU.index_id = I.index_id AND ixU.object_id = I.object_idINNER JOIN sys.objects AS O ON ixU.object_id = O.object_idWHERE ixU.database_id = DB_ID()ORDER BY ixU.index_id ASC;

Query 2: Index Scans

Similar to the behavior we observed for inserts/writes we see the effects of scans in an equal fashion. I’m going to perform 10 read operations utilizing the following query which will perform that SELECT statement 10 times consecutively:

SELECT DBTypeID ,   DBType FROM lifeboat.dbo.DatabaseTypes;GO 10

The query performs an index scan on the non-clustered index as can be seen in the execution plan:

Querying the sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats Dynamic Management Objects we see divergent results between the two objects:

SELECT  --IDENTIFICATION:DB_NAME(ixO.database_id) AS database__name,O.name AS object__name,I.name AS index__name,I.type_desc AS index__type,--LEAF LEVEL ACTIVITY:    ixO.leaf_insert_count ,ixO.range_scan_count,--LOCKING ACTIVITY:    ixO.row_lock_count ,    ixO.page_lock_count ,--LATCHING ACTIVITY:ixO.page_io_latch_wait_countFROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ixOINNER JOIN sys.indexes I ON ixO.object_id = I.object_id AND ixO.index_id = I.index_idINNER JOIN sys.objects AS OON O.object_id = ixO.object_idWHERE O.is_ms_shipped = 0;SELECT O.name AS object__name,I.name AS index__name,I.type_desc AS index__type,ixU.user_seeks,ixU.user_scans,ixU.user_lookups,ixU.user_updates AS total_user_writesFROM sys.dm_db_index_usage_stats AS ixUINNER JOIN sys.indexes AS I ON ixU.index_id = I.index_id AND ixU.object_id = I.object_idINNER JOIN sys.objects AS O ON ixU.object_id = O.object_idWHERE ixU.database_id = DB_ID()ORDER BY ixU.index_id ASC;

sys.dm_db_index_operational_stats:

sys.dm_db_index_usage_stats:

Observe the individual range scans occurring against the non-clustered index. Note that since these are separate queries performed against the database that they are recorded as such in sys.dm_db_index_usage_stats. This is one of the few times both constructs yield similar results. That changes as we move on to seeks.

Query 3: Index Seeks

We would expect to observe the effects of index seeks in an equal fashion as scans. This time around I need to make use of a much larger index since the one we’re working with is so small a scan is considered appropriate by the Query Optimizer.  Compare the following execution plan (the underlying query is not relevant to the topic at hand) to the results from sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats against a restarted instance of SQL Server where all previous values for that DMF were 0 using the same exact diagnostic queries used above.  To be clear this is a simple index seek operation as demonstrated below in the execution plan:

The results are a bit surprising at first blush for the operational stats DMF but only because I’m leaving out a vital piece of information:

sys.dm_db_index_operational_stats:

sys.dm_db_index_usage_stats:

Initially we don’t see any differentiation between seeks and scans in sys.dm_db_index_operational_stats.  Matter of fact the Index Seek operation is recorded as a single range scan inside of this DMF in this case. On the other hand expectations are met regarding sys.dm_db_index_usage_stats. In that system view this operation is recorded as a user_seek.  Why do you think this is the case?

The one thing I didn’t mention is that multiple results were returned for this query. Oh, and the results could be satisfied through a single range scan. Therefore the metadata exposed in sys.dm_db_index_operational_stats is precisely what we’d expect to see. It’s actually sys.dm_db_index_usage_stats that isn’t being 100% honest with us. Yes, the operation was an index seek as shown in the execution plan, however under the covers this was really a range scan so one has to wonder whether presenting the impact in the usage stats DMV is correct as a seek or as a scan.

Conversely if I execute a query that returns a single row the execution plan looks identical in everything but index name as the target of the operation but the operation manifests itself as a seek in sys.dm_db_index_operational_stats rather than a range scan:

Query 4: Lookups

So far we’ve covered writes/updates as well as seeks and scans. There is one more read operation to consider: lookups.  Lookups happen when you return results using the non-clustered index yet not all columns required are either part of – or an included column of – the non-clustered index. The operation collects the columns that are a part of the non-clustered index then performs a row lookup to the data to collect the remainder of the columns necessary to satisfy the request. In the query plan below you’ll see that a request is made, columns are provisioned from the non-clustered index (which happens to be a unique non-clustered index in this case) and then since there is no clustered index performs a RID lookup to satisfy the query:

This time we get the results closer to what we would expect to see from both sys.dm_db_index_operational_stats as well as sys.dm_db_index_usage_stats:

sys.dm_db_index_operational_stats:

sys.dm_db_index_usage_stats:

The operational stats DMF presents the RID lookup as a singleton lookup against the non-clustered index to collect the single returned row’s data from the index and another singleton lookup against the heap to complete the collection process to satisfy the needs of the request.  Then there are associated page locks taken on both the non-clustered index and the heap as well as a row lock on the non-clustered index.  Meanwhile the usage stats DMV presents the exectuted query’s actions as a seek against the non-clustered index and a subsequent lookup against the heap.

What happens if there was a clustered index on the table? It depends on whether the query can be satisfied from a combination of the non-clustered and clustering keys or if not.

If the columns requested through the query can be satisfied through a combination of the non-clustered and clustered index then you have a straight-forward index seek against the non-clustered index since the non-clustered index includes the clustering key(s) implicitly. If the combination of indexes still does not cover all columns requested then the results in both DMOs are identical to what was shown above except the lookup is against the clustered index as a key lookup and not the heap as a RID lookup.

Conclusion of Part One

Hopefully this excursion has provided insight into how query operations manifest their activity in both sys.dm_db_index_operational_stats as well as sys.dm_db_index_usage_stats. Going forward we’ll rely on your understanding of these foundational rules as we use the sys.dm_db_index_operational_stats DMF as a tool for performance tuning both reactively and in a proactive manner.