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_id

SELECT 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.