What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?
SQL Server includes two DMVs - sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats - that are extremely useful for monitoring which indexes are used as well as how and when they are used. Both DMVs report similar statistics on information such as the number of scans, seeks, and updates to different indexes. These DMVs are documented in Books Online (see here and here) and a simple Web search reveals numerous other postings about these DMVs. However, in my own search, I did not find many direct explanations of the difference between these two valuable DMVs. (You will find a short explanation halfway through this post on the Microsoft SQL Server Customer Advisory Team blog.)
The main difference between these DMVs is simple but important:
sys.dm_db_index_usage_stats records how many times the query optimizer uses an index in a plan. This usage information is recorded again each time the plan is executed. (Compiling a plan alone is not sufficient to record an index's usage.) However, and this is the important part, for the purposes of computing the statistics, it does matter how many times the query processor executes the specific operator that references the index. For that matter, it does not matter whether the query processor executes the operator at all. Mere execution of the plan counts as a single usage for each index used by the plan.
sys.dm_db_index_operational_stats records how many times the storage engine executes a specific operation on the index. These statistics do depend on how many times the query processor executes each operator. If an operator is never executed, the storage engine does not perform any operations on the index and the DMV reports that the index was not used. If an operator is executed multiple times, the storage engine performs multiple operations on the index and the DMV reports that the index was used multiple times.
Update (7/29/2009): The following paragraph is incorrect. See this post for more information.
(Another less important difference between these DMVs is that sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted while sys.dm_db_index_operational_stats reports on all indexes regardless of whether they have been used.)
Let's try an example to see this difference in action. I'll use the following simple schema:
CREATE TABLE T (A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TA ON T(A)
CREATE UNIQUE INDEX TB ON T(B)
As expected, immediately after creating this table, the stats are zero (or just non-existent):
SELECT index_id, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('tempdb') and object_id = OBJECT_ID('tempdb..t')
ORDER BY index_idSELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (DB_ID('tempdb'), OBJECT_ID('tempdb..t'), NULL, NULL)
ORDER BY index_id
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------------
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 0 0
2 0 0
Now suppose that we do a scan of the clustered index:
SELECT * FROM T
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))
Repeating the DMV queries, we see that the clustered index shows one scan in both DMVs. SQL Server records the scan even though the table contains no rows and the query returns an empty result:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------------
1 0 1 0 0
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 1 0
2 0 0
Next let's try a singleton lookup on the clustered index:
SELECT * FROM T WHERE A = 1
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
Again the table contains no rows and the query returns an empty result. Nevertheless, the DMVs now report one seek and one singleton lookup:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------------
1 1 1 0 0
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 1 1
2 0 0
(Keep in mind that the DMV results are cumulative so you need to subtract the previous values from the current values as you run each of these experiments. Thus, we can disregard the scan that was already reported by the previous example.)
Now let's try something a little more interesting. Let's run a bookmark lookup:
SELECT * FROM T WHERE B = 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[T].[TB]), SEEK:([tempdb].[dbo].[T].[B]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=[tempdb].[dbo].[T].[A]) LOOKUP ORDERED FORWARD)
As expected sys.dm_db_index_usage_stats reports a seek on index TB (index id 2) and a bookmark lookup on the clustered index (index id 1). However, sys.dm_db_index_operational_stats reports only the singleton lookup on index TB but does not report any new activity on the clustered index:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------------
1 1 1 1 0
2 1 0 0 0
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 1 1
2 0 1
To understand what has happened, recall how a nested loops join works. The server executes the seek (the singleton lookup) on index TB and, as in the previous example, both DMVs are updated even though the seek returns no rows. However, since the seek on index TB returns no rows, the nested loops join does not execute the clustered index seek (i.e., the bookmark lookup). The server updates sys.dm_db_index_usage_stats to indicate that it executed a query plan that includes a bookmark lookup on table T, but does not update sys.dm_db_index_operational_stats since the query did not actually perform any bookmark lookups.
Next, let's insert three rows into the table and run another bookmark lookup experiment. I'm using a hint to force a bookmark lookup plan. Without the hint, the optimizer would simply use a clustered index scan since the query returns all three rows in the table:
INSERT T VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2)
SELECT * FROM T WITH (INDEX (TB))
|--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[T].[A]))
|--Index Scan(OBJECT:([tempdb].[dbo].[T].[TB]))
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[TA]), SEEK:([tempdb].[dbo].[T].[A]=[tempdb].[dbo].[T].[A]) LOOKUP ORDERED FORWARD)
This time sys.dm_db_index_usage_stats reports a scan on index TB and a bookmark lookup on the clustered index (plus the updates from the insert statement). But, sys.dm_db_index_operational_stats reports a scan on index TB and three bookmark lookups on the clustered index:
index_id user_seeks user_scans user_lookups user_updates
----------- -------------------- -------------------- -------------------- --------------------
1 1 1 2 1
2 1 1 0 1
index_id range_scan_count singleton_lookup_count
----------- -------------------- ----------------------
1 1 4
2 1 1
When the server executes the above query, it runs the clustered index seek three times - once for each row returned by the index scan. We ran the query only once but it performed three bookmark lookups. Thus, as in the prior example, the server updates sys.dm_db_index_usage_stats to indicate that it executed a query plan that includes a bookmark lookup on table T, but unlike the prior example, it updates sys.dm_db_index_operational_stats to indicate that the query performed three actual bookmark lookups.
I've used bookmark lookups in the above examples, but any nested loops join will produce similar results. At this point, it should be clear that the statistics returned by these two DMVs can differ dramatically.
So, what is the important takeaway from all of these examples? Don't expect the data reported by these two DMVs to match. sys.dm_db_index_usage_stats tells us the proportion of query plans that were executed that use various indexes. This information is useful for concluding how many of the executed query plans might be affected if we drop an index but it does not tell us how many actual operations are performed using each index. sys.dm_db_index_operational_stats, on the other hand, tells us how often the indexes are actually used during the execution of plans and, thus, which indexes are directly contributing to server performance. But, even if sys.dm_db_index_operational_stats indicates that an index is not used very often (or perhaps even that an index is never used), do not automatically conclude that you can drop the index. First, be sure that sys.dm_db_index_usage_stats indicates that no queries depend on the index. In some cases, the presence of an index could change a query plan for the better even though the index itself is not used when the plan is executed.
Comments
Anonymous
November 03, 2008
Hello CraigIt is very useful post. I use SQL Server 2005 and looking at sys.dm_db_index_operational_statsDMV to get the info about one of my table I get more than 20 rows , something like that index=2index_id,range_scan_count,singleton_lookup_count2 1 902 1 22 0 92 0 02 4 02 1 02 0 02 0 02 25 392 790 12 32779 42How do I know that index=2 is useful? Why do I have lots of rows for index=2. The above output seems that I have lots of ranges and less singleton_lookup_count which means that I need to examine this index ,right?Than youAnonymous
November 05, 2008
I can think of two reasons why you might see multiple rows for index_id 2. First, if you use the OBJECT_ID function but it fails, it may return NULL which would cause the DMV to return rows for all objects. Second, if you have a partitioned table, you will get one row per partition. Try selecting the database_id, ojbect_id, and partition_number columns from the DMV to get more information about these rows.Anonymous
November 05, 2008
Hello CraigYes you are right.OBJECT_ID function fails and returns NULL.I do not have partitioned table hence it returns 1 for partition_number column.But why OBJECT_ID has been failed, and one more question if have a large number for then range_scan_count and 0 for singleton_lookup_count does that mean the index is unuseful/needed to be tracked down, am I right?Anonymous
November 06, 2008
OBJECT_ID could fail for many reasons. If you have not already, try specifying the full three part name for the object and make sure if you have a case sensitive collation that you spelled the entire name correctly.If either range_scan_count or singleton_lookup_count is non-zero, the index is being used. Moreover, as I noted in my post, even if both fields are zero, you still need to check sys.dm_db_index_usage_stats before you drop the index.Anonymous
July 01, 2010
>>>But, even if sys.dm_db_index_operational_stats indicates that an index is not used very often (or perhaps even that an index is never used), do not automatically conclude that you can drop the index. First, be sure that sys.dm_db_index_usage_stats indicates that no queries depend on the index. In your example I did not see anywhere that if sys.dm_db_index_usage_stats got updated and sys.dm_db_index_operational_stats did not. So in that case it looks sys.dm_db_index_operational_stats data is sufficient to beleive that an index is being used or not?Anonymous
July 09, 2010
The simplest example of how sys.dm_db_index_operational_stats can mislead is an index nested loops join where the outer side of the join does not return any rows. In this case, just as in the bookmark lookup example above, the index seek on the inner side of the join will not be executed and sys.dm_db_index_operational_stats will not be updated. However, removing the "unused" index could force the optimizer to choose an entirely different plan.