What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

re: What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

Uri Dimant — Tue, 04 Nov 2008 07:28:45 GMT

Hello Craig

It is very useful post. I use SQL Server 2005 and looking at sys.dm_db_index_operational_stats

DMV to get the info about one of my table I get more than 20 rows , something like that index=2

index_id,range_scan_count,singleton_lookup_count

2 1 90

2 1 2

2 0 9

2 0 0

2 4 0

2 1 0

2 0 0

2 25 39

2 790 1

2 32779 42

How 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 you

re: What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

craigfr — Wed, 05 Nov 2008 20:43:28 GMT

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.

re: What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

Uri Dimant — Thu, 06 Nov 2008 08:48:31 GMT

Hello Craig

Yes 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?

re: What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

craigfr — Fri, 07 Nov 2008 04:34:25 GMT

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.