Grumpy Old DBA - Beware the darkside !

sys.dm_db_index_usage_stats does it tell the truth ?

Here’s what is in BOL 2008 , also in BOL 2005 , also in the first few blog posts about the this dmv that BING brought back, it’s also in my “Analysing Indexes” blog posts.

“ Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view.

Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view.

You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead.

You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down

(for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero. “

From Microsoft BOL SQL 2008.

Well here I am trying to identify tables which only have inserts or tables which should not have updates, only inserts, a slight variation on the same but critically different < grin >

I just need to explain how I can use the dmvs in this way. As part of our production release process we fail over to the alternate node of the cluster on each release,

this means we run fully on both nodes , not at the same time of course, and therefore we have confidence that in a failure we can run our system on either node,

this may seem pointless but in the vein of “ The only good backup is one that has been successfully restored “ if you don’t run on alternate nodes how do you know they will work?

A side effect of this is that the dmvs are reset on each release so to a certain extent I can look for differences in behaviour caused by the release.

Well I have a couple of tables I wish to partition and compress, these tables should only have inserts, there are no deletes, so I only need to check for updates.

declare @table sysname='dbo.xxxxxx'
--
-- return index usage
--
select index_id,user_seeks,user_scans,user_lookups,user_updates,last_user_seek,last_user_scan,last_user_lookup,last_user_update  
from sys.dm_db_index_usage_stats 
where database_id = 7 and [object_id]=object_id(@table);

Here’s the results form the query I have just run :

This indicates 25,413 changes – my next step is to compare the physical rowcounts of the table at the release with the current rowcount, hopefully these numbers should compare at some sort of level.

How do I know my rowcount before you may ask – we take a backup prior to the release and restore this as a “Previous Version “ so that we can investigate and regression of fixes,

or just prove something was really that way before we did the last release!

A quick check of the row counts shows that the table has grown by 448,433 rows.

Now I expect a few readers might be saying – “ ah you did something to clear the dmvs “ – well this might be true except – I capture the contents of sys.dm_db_index_usage_stats into

another table every night, so it’s easy to run a query to see exactly where we cleared the dmvs. ( only we didn’t )

If you’ve read my posts on “ Analysing Indexes “ you’ll know that I recommend capturing dmv content if you plan to do any work based upon them.

In general I find the dmvs to be very useful, however in this instance for the two tables I am interested in the stats are incorrect according to what you might expect.

There is of course sys.dm_db_index_operational_stats

BOL state : “ 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). “

I should say that I’ve not encountered this before, usually the updates total fits in with the growth of the table, my server isn’t in any real terms struggling for resource,

It’s X64 SQL 2008 Ent and closely monitored with lots of available performance stats .