dm_db_index_usage_stats - Multiple entries for a single index

Question

dm_db_index_usage_stats - Multiple entries for a single index

pjdiller

SSC Eights!

Points: 977
More actions
April 19, 2010 at 8:48 am

#223542

I am attempting to gather information about index utilization using dm_db_index_usage_stats, and felt like I was making some headway.
Then I discovered that for a given index, I could have several rows. (I ran it on a SharePoint content DB for reference and found that the DMV returned 20 rows for the WebParts_PK index on the WebParts table.)
At first I thought it was because I had made a mistake in a join somewhere, but when I ran the DMV alone, I got the 20 rows anyway. Each row has unique information regarding user seeks, updates, etc. It also lists different "last seek," "last etc..." date/time values for each row returned.
So far I have found the question asked once but not answered, and I have unsuccessfully tried to find the answer in BOL.
1. Should this behavior be expected?
2. Can I sum the columns to get an overall result?
3. What causes a new row?
Thanks for your time!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I would not expect this behavior. I haven't been able to reproduce the behavior. When was the last time you updated your statistics? Rebuilt indexes?

Also, what is the query you are using to query this information?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

roger.price-1150775 Default port Points: 1458 More actions · Answer 2

Seen this with partitioned tabled. You will get one row for each partition.

roger.price-1150775 Default port Points: 1458 More actions · Answer 3

The data is incremental. The totals are not all that useful but I have found it useful to collect/sample the data regularly, perhaps every hour. Then compare each sample with the last to see the numbers for the last hour. This can shows some interesting trends with and access patterns. Consider storing the sampled data on another server so that your analysis of the data does not impact your production server. I do this with several DMV's including sys.dm_exec_query_stats. This means I can see different access patterns in dm_db_index_usage_stats and look at the db_exec_query_stats data to see what sprocs/queries are generation those usage patterns.