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.
SELECT DB_NAME(s.database_id) AS DBNAME, OBJECT_NAME(s.OBJECT_ID) AS ObjectName
, i.name AS IndexName
,user_seeks, user_scans,user_lookups,user_updates,system_seeks, system_scans
FROM sys.dm_db_index_usage_stats s
INNER Join sys.indexes i
ON i.OBJECT_ID = s.OBJECT_ID
And i.index_id = s.index_id
And s.database_id = DB_ID()
WHERE OBJECTPROPERTY(s.OBJECT_ID,'ISMSShipped') = 0
GROUP BY s.database_id,s.OBJECT_ID,i.name,s.index_id, user_seeks, user_scans
,user_lookups,user_updates,system_seeks, system_scans
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.
SELECT DB_NAME(s.database_id) AS DBNAME, OBJECT_NAME(s.OBJECT_ID) AS ObjectName
, i.name AS IndexName
,user_seeks, user_scans,user_lookups,user_updates,system_seeks, system_scans
,Row_number() OVER (partition BY s.OBJECT_ID,s.index_id ORDER BY s.index_id) AS RowNum
FROM sys.dm_db_index_usage_stats s
INNER Join sys.indexes i
ON i.OBJECT_ID = s.OBJECT_ID
And i.index_id = s.index_id
And s.database_id = DB_ID()
WHERE OBJECTPROPERTY(s.OBJECT_ID,'ISMSShipped') = 0
GROUP BY s.database_id,s.OBJECT_ID,i.name,s.index_id, user_seeks, user_scans
,user_lookups,user_updates,system_seeks, system_scans
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.
SELECT DB_NAME(s.database_id) AS DBNAME,s.database_id, OBJECT_NAME(s.OBJECT_ID) AS ObjectName, i.name AS IndexName
,Row_number() OVER (partition BY s.OBJECT_ID,s.index_id ORDER BY s.index_id) AS RowNum
FROM sys.dm_db_index_usage_stats s
INNER Join sys.indexes i
ON i.OBJECT_ID = s.OBJECT_ID
And i.index_id = s.index_id
And s.database_id = DB_ID()
WHERE OBJECTPROPERTY(s.OBJECT_ID,'ISMSShipped') = 0
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.
SELECT OBJECT_NAME(s.OBJECT_ID) AS ObjectName, i.name AS IndexName
,Row_number() OVER (partition BY s.OBJECT_ID,s.index_id ORDER BY s.index_id) AS RowNum
FROM sys.dm_db_index_usage_stats s
INNER Join sys.indexes i
ON i.OBJECT_ID = s.OBJECT_ID
And i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,'ISMSShipped') = 0
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.
SELECT DB_NAME(s.database_id) AS DBNAME,s.database_id, OBJECT_NAME(s.OBJECT_ID) AS ObjectName, i.name AS IndexName
,Row_number() OVER (partition BY s.OBJECT_ID,s.index_id ORDER BY s.index_id) AS RowNum
FROM sys.dm_db_index_usage_stats s
INNER Join sys.indexes i
ON i.OBJECT_ID = s.OBJECT_ID
And i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,'ISMSShipped') = 0
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?
