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 :

 

index_id

user_seeks

user_scans

user_lookups

user_updates

last_user_seek

23

730313

0

0

25413

2010-08-16 14:01:57.287

4

528189

0

0

25414

2010-08-16 14:01:57.353

3

31429

0

0

25413

2010-08-16 14:00:30.297

5

1884831

0

0

25413

2010-08-16 14:01:59.610

2

174777

0

0

25414

2010-08-16 14:01:54.207

1

1180878

1

2641380

25413

2010-08-16 14:01:58.910

8

894509

0

0

25413

2010-08-16 14:01:59.610

 

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 )

 

TheDate

database_id

object_id

index_id

user_seeks

user_scans

user_lookups

user_updates

last_user_seek

last_user_scan

last_user_lookup

last_user_update

2010-08-15 23:59:00.073

7

713873710

1

0

0

592750

24,545

NULL

NULL

2010-08-15 19:56:52.287

2010-08-15 23:43:44.777

2010-08-14 23:59:00.473

7

713873710

1

0

0

587266

24,530

NULL

NULL

2010-08-14 20:55:28.807

2010-08-14 23:55:00.513

2010-08-13 23:59:00.717

7

713873710

1

0

0

539002

24,393

NULL

NULL

2010-08-13 23:54:38.443

2010-08-13 23:58:14.347

2010-08-12 23:59:00.637

7

713873710

1

0

0

315703

21,719

NULL

NULL

2010-08-12 23:56:29.647

2010-08-12 23:58:46.380

2010-08-11 23:59:00.300

7

713873710

1

0

0

272411

18,416

NULL

NULL

2010-08-11 23:44:40.793

2010-08-11 23:58:13.800

2010-08-10 23:59:00.207

7

713873710

1

0

0

254353

15,912

NULL

NULL

2010-08-10 23:47:08.997

2010-08-10 23:58:17.447

2010-08-09 23:59:00.183

7

713873710

1

0

0

236541

13,470

NULL

NULL

2010-08-09 23:40:29.203

2010-08-09 23:57:13.700

2010-08-08 23:59:00.297

7

713873710

1

0

0

232041

11,246

NULL

NULL

2010-08-08 19:56:47.750

2010-08-08 22:30:21.023

2010-08-07 23:59:00.823

7

713873710

1

0

0

229782

11225

NULL

NULL

2010-08-07 22:59:53.893

2010-08-07 21:01:46.430

2010-08-06 23:59:00.270

7

713873710

1

0

0

135971

11110

NULL

NULL

2010-08-06 23:58:12.200

2010-08-06 23:58:12.243

2010-08-05 23:59:00.113

7

713873710

1

0

0

82844

8967

NULL

NULL

2010-08-05 23:44:02.840

2010-08-05 23:57:24.097

2010-08-04 23:59:00.513

7

713873710

1

0

0

63809

6529

NULL

NULL

2010-08-04 23:53:32.790

2010-08-04 23:58:20.303

2010-08-03 23:59:00.340

7

713873710

1

0

0

46032

4266

NULL

NULL

2010-08-03 23:43:42.380

2010-08-03 23:54:22.757

2010-08-02 23:59:00.030

7

713873710

1

0

0

26455

2015

NULL

NULL

2010-08-02 23:39:24.753

2010-08-02 23:55:30.687

2010-08-01 23:59:00.270

7

713873710

1

0

0

19021

62

NULL

NULL

2010-08-01 19:56:53.570

2010-08-01 20:43:00.690

2010-07-31 23:59:00.917

7

713873710

1

0

0

134

41

NULL

NULL

2010-07-31 21:57:38.773

2010-07-31 23:11:22.767

 

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 .

Published 16 August 2010 18:37 by GrumpyOldDBA
Filed under: ,

Comments

# ????????????????????????????????????????????? | ?????????blog

Pingback from  ????????????????????????????????????????????? | ?????????blog