Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Index Stats Duplication

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?

Comments

Posted by Steve Jones on 23 April 2010

That's very interesting. It could have implications if you have test/dev databases sharing an instance with production, or with each other, and you are trying to go through testing and determing usage.

Posted by SQL Swerver on 29 July 2010

I've just encountered this issue whilst building some scripts for auditing our index requirements.

I get identical records returned when joining sys.dm_db_index_usage_stats to other sys views.

I would go as far as saying there was a bug in sys.dm_db_index_usage_stats which is also evident in SQL 2008.

Posted by SQL Swerver on 29 July 2010

...further to this I noticed that the sys view sys.dm_db_index_usage_stats returns rows with identical  column-values 'index_id' when JOINs to other system views are implemented.

For example, if there are two indexes on a table, joining this view to sys.objects to query those indexes actually returns four rows, two rows for each index. To see what I mean, compare the index_id column-value returned from sys.dm_db_index_usage_stats when joined with sys.indexes (index_id).

Posted by SQLServerCentral on 29 July 2010

What's very strange is that this behaviour even occurs if you populate temporary tables with the output of the system views individually, then join the two temp tables to get your results. I still get two lines for each index, weird!

Posted by SQLServerCentral on 29 July 2010

Intgerestingly, when 'actual' tables are used (as opposed to temporary tables) to store the View's output, and then subsequently joined to combine the columns in the output, this duplication does not occur!

My conclusion: the system view "sys.dm_db_index_usage_stats" returns one line for every database involved in its use(TempDB, source DB, etc).

Posted by SQL Swerver on 29 July 2010

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.) I Declare and populate '@Indexes table' (or #Indexes) with the required info from a join of sys.indexes and sys.objects.

2.) Then Declare and populate '@IndexStat table' (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 in each table-variable. However:

4.) SELECT ... inner Join on object_id between @Indexes (or #Indexes) and @IndexStat (or #IndexStat) STILL returns duplicate rows. How weird is that!?!?!

Posted by gunter.eifler on 20 June 2011

Looking at monitoring indexes for usage stats as a feeder to a fragmentation strategy, and the stats are clearly ambiguous. With two sets of results for each index, which to use? Or should i be addnig them together and use the total?

Posted by SQL Swerver on 17 August 2012

I've been working on a different DB platform (Interbase - unfortunately) since I first logged my comments on this issue and I see it's returned to bite me now I'm back in the SQL Server world - and getting to grips with my new employer's indexing.

I still can't find any definitive answers regarding this issue from anywhere.

Posted by Eric Mamet on 3 March 2014

Running a simple query in the appropriate database seems to indicate that the proper counters (associated with the right database) are increased.

From that, I would be tempted to believe that querying this DMV is fine as long as one filters on the database_id column as well.

Posted by SQL Swerver on 23 May 2014

Re: "querying this DMV is fine as long as one filters on the database_id column "

This isn't the case it appears, I get three entries returned for every Index on a given table, in each row the database_id value is identical.

Leave a Comment

Please register or log in to leave a comment.