Correction to my prior post on sys.dm_db_index_operational_stats

  • Article

In this post about the sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats DMVs, I wrote:

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.

The Books Online page for sys.dm_db_index_operational_stats similarly states about the object_id parameter: "Specify NULL to return information for all tables and views in the specified database."  However, further down in the same page, it states:

The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).

An observant reader pointed out that the sys.dm_db_index_operational_stats DMV was not behaving as I claimed - that it was not returning all rows - and further pointed out the apparent discrepancy in Books Online.  I checked with one of the storage engine developers who confirmed for me that the DMV will only return rows for those objects that are currently in the metadata cache.

The metadata cache does not contain any user tables when the server is first started or, for a database, when it is first attached.  Moreover, if a database contains many tables, the cache may not be large enough to store all of them at once and some may be evicted.  If a table is evicted, it will cease to appear in the DMV output and its counters will be reset to zero.

Let's look at a simple example.  First, let's create a test database:

CREATE DATABASE DMVTest
GO
USE DMVTest
GO
CREATE TABLE T (A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TA ON T(A)
CREATE UNIQUE INDEX TB ON T(B)
GO

Since we just created this table, it should be in the metadata cache and should be listed by sys.dm_db_index_operational_stats:

SELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID('T'), NULL, NULL)
ORDER BY index_id

 index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           0                    0
2           0                    0

Let's also run a simple query so that the table appears in sys.dm_db_index_usage_stats.  Recall that sys.dm_index_usage_stats only lists tables and indexes that appear in a query plan and only when that query plan is actually executed.

SELECT * FROM T

SELECT index_id, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() and object_id = OBJECT_ID('T')
ORDER BY index_id

 index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------
1           0                    1                    0                    0

Now, let's detach and reattach the database to clear the metadata cache:

USE tempdb
GO
EXEC SP_DETACH_DB 'DMVTest'
GO
EXEC SP_ATTACH_DB 'DMVTest',
      'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DMVTest.mdf',
      'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DMVTest_log.ldf'
GO
USE DMVTest
GO

Rechecking the two DMVs shows that the table is not listed in either one:

SELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID('T'), NULL, NULL)
ORDER BY index_id

 index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------

SELECT index_id, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID() and object_id = OBJECT_ID('T')
ORDER BY index_id

 index_id    user_seeks           user_scans           user_lookups         user_updates
----------- -------------------- -------------------- -------------------- --------------------

Finally, we can repopulate the metadata cache by compiling a query that references the table.  Note that compiling the query is all that is needed.  There is no need to execute it.

SET SHOWPLAN_TEXT ON
GO
SELECT * FROM T
GO
SET SHOWPLAN_TEXT OFF
GO

Rechecking sys.dm_db_index_operational_stats one final time shows that the table is once again listed:

SELECT index_id, range_scan_count, singleton_lookup_count
FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID('T'), NULL, NULL)
ORDER BY index_id

 index_id    range_scan_count     singleton_lookup_count
----------- -------------------- ----------------------
1           0                    0
2           0                    0

Note that all of the indexes, not just the clustered index (which happens to be used by the table scan in the above query plan), are listed.  In general, SQL Server loads all of a table's metadata as part of the compilation process.

In this simple example, I used database attach and detach to flush the metadata cache.  Of course, SQL Server also starts with an empty metadata cache after a restart and, as I noted above, may evict metadata at any time due to memory pressure.  Thus, Books Online is correct in stating that sys.dm_db_index_operational_stats may not offer reliable data regarding a table's past usage.