Index Stats Duplication

Tags: ,
Comments: 2 Comments
Published on: April 21, 2010

I came across a recent posting about seeing multiple entries in sys.dm_db_index_usage_stats for the same index.  This kind of behavior can be somewhat concerning since that view should have a unique entry for each index.  As described by Microsoft, this view

“Returns counts of different types of index operations and the time each type of operation was last performed.”

You can read more about it here.  Further in the same document, one will read

“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.”

Thus, I started checking to see if I could reproduce the same sort of results.  While investigating, I determined that I should Join that view to the sys.indexes view.  Since the sys.dm_db_index_usage_stats view does not contain an Index Name, I felt it necessary to be able to see the name and not just a number.

My first run at the query to try and reproduce the results is displayed below.

[codesyntax lang=”tsql”]


Well this appears to produce results that are consistent with the description offered by Microsoft.  So I wanted to verify and added a windowing function into the mix.

[codesyntax lang=”tsql”]


You will notice the line with Row_Number().  This gives me a count of each time that IndexName appears in the system view.  Well, now having verified further, I still have results consistent with the documentation from Microsoft.  I decided to work backwards from this script (yeah I know).  I will reduce the number of fields I have returned and try to get a duplication on a base level and then re-expand the fields being gathered.  The next stab at this looked like the following script.

[codesyntax lang=”tsql”]


So for this revision, I simply removed any of the counter fields and the group by clause.  I was hoping that my Group By would reveal the answer.  Lo and behold, I am still unable to reproduce the results.  Ok, time to trim a little bit more as I work backward trying to create the same results.

[codesyntax lang=”tsql”]


This time, I removed a Join condition of s.database = db_id() and the database_id and DatabaseName.  When I run the query now, I start to see the same results.  So the immediate indicator to me is that there is a duplication of indexes but not within the same database.  The duplication appears across different databases.  To verify, I know need to expand my query just a bit.

[codesyntax lang=”tsql”]


Here, I was able to verify that the places where RowNum did not equal one – I had an index in a different database by the same name.  Not only is the index name the same, but the Object holding that index is also the same in the other database.  Now, if I want, I can expand my query all the way back to the original query knowing that I verified that the entries in sys.dm_db_index_usage_stats are unique for each index.  The conclusion I would draw from this exercise is that a query can have an impact on the results displayed.  One should check and then double check to make sure the results jive.  Try the query a few different ways and see if the results are consistent.  If not, what factor in the query was changed that changed your results?

Or is it?

While exploring this further I decided to verify some of the objects in different databases.  For instance, I might see the object being reported in the ReportServer database, and then also in msdb and then again in another database.  But the object only exists in ReportServer.  Why is this happening?  My thoughts on this are currently just an educated guess.  It appears that a connection is established in one database and then makes a call to an object in a second database and causing a use against the actual index in the call to the second database.  The record gets inserted into the metadata tables that this view pulls from with each database that was touched – but each time with the object_id and index_id of the second database.  Does that make sense?

2 Comments - Leave a comment
  1. […] This post was mentioned on Twitter by Dukagjin Maloku, Jason Brimhall. Jason Brimhall said: New post: Index Stats Duplication ( […]

  2. Timothy Garza says:

    Hi Jason, I’ve been tackling this particular problem myself recently. I’ve noticed this same behaviour even if I populate local variables or temp tables with the data.
    1.) create and populate @Indexes (or #Indexes) with the required info from a join of sys.indexes and sys.objects.
    2.) create and populate @IndexStat (or #IndexStat) with the required info from sys.dm_db_index_usage_stats
    3.) SELECT * FROM @Indexes, @IndexStats, etc confirms one-for-one rows of data. However:
    4.) An inner Join on object_id between @Indexes (or #Indexes) and @IndexStat (or #IndexStat) STILL returns duplicate rows. How weird is that!?!?!

Leave a comment

Your email address will not be published. Required fields are marked *

April 2010
« Mar   May »


  • @jdanton: @mobileck It 100% depends, but on 2014 by default I think it’s 3 MB (log+data). But will orders (100s-1000s) more on busy system #sqlhelp
  • @mobileck: Anyone know the minimum amount of space tempdb will use on a new instance (including system objects)? Seeing 2.75MB on 2014 #sqlhelp
  • @SQLSoldier: @MidnightDBA SMO via .Net. Why no PowerShell. PowerShell works with 2008 +. #sqlhelp
  • @MidnightDBA: Server: No SSMS, no powershell, SQL 08 r2. Best TSQL script to create sp_add_job statements, et al, for all jobs on the instance? #sqlhelp
  • @AMtwo: @ajneuman AFAIK, the only way to upgrade is to point SSRS at the DB, and let it do it's thing. What error do you get on upgrade? #sqlhelp
  • @ajneuman: Is there a way to manually upgrade the Report Server databae? It's failing to upgrade from version 147 to 174. #sqlhelp #SSRS #sqlserver2016
  • @AngryPets: @SQLYard #sqlhelp Nope - they just need to be sized to handle the indexes on them. I'd check file stalls vs all files to see if helping/etc.
  • @SQLPrincess: #sqlhelp Any ideas on how to reverse engineer a data flow diagram from queries to show the data transformations?
  • @SQLYard: @AngryPets Thnks so the 3 filegroups different sizes are normal then? I was thinking they needed to be aligned in size. #sqlhelp
  • @AMtwo: @SqlRyan If you want to use source control with your report designer, use Visual Studio + SSDT. #sqlhelp

Welcome , today is Saturday, October 1, 2016