Non-Unique, Non-Consistent Data Returns From sys.dm_db_index_operational_stats
I’ve run across an interesting (and by interesting I mean annoying) issue as of late with persisting data from the sys.dm_db_index_operational_stats. This DMF (Dynamic Management Function) returns information concerning how existing indexes are being utilized by the SQL Server 2005 and newer instance. It contains granular I/O locking and latching metrics for each database, object, index, and partition thereof on the instance. The combination of these columns should be unique. What I’ve discovered however is that sometimes, in some manners, this depends.
I persist this data in a physical table four times throughout the day in order to ensure that I have a decent pool of metadata for analysis of index usage whenever the cache storing this information is cleared, as is the case whenever the SQL Server services are restarted. I do so through a stored procedure called via a scheduled SQL Server Agent job running on the instance. I’ve highlighted this process on MSSQLTips.com here.
Just recently the job began to fail:
Msg 2627, Sev 14, State 1, Line 193 : Violation of PRIMARY KEY constraint 'PK_dm_db_index_operational_stats'. Cannot insert duplicate key in object 'MetaBot.dm_db_index_operational_stats'. [SQLSTATE 23000]
I created a clustered index on the combined fields of database_id, object_id, index_id, and partition_number for the physical table, as these values together should be unique IN ALL CIRCUMSTANCES. However, as you’ll soon see that is not always the case.
Take for example the situation I encounter when I run the following query against sys.dm_db_index_operational_stats() using parameters to drill into the specific database, object, index, and partition_number (respectively) for which I am encountering this issue:
SELECT * FROM sys.dm_db_index_operational_stats(99,13, 1, 1)
Now take a look at the result set when I run the following query:
SELECT *
FROM sys.[dm_db_index_operational_stats](NULL, NULL, NULL, NULL)
ORDER BY database_id, OBJECT_ID, index_id, partition_number
I have been able to replicate this issue with two different index partitions as you can see above. I’ve included some of the additional columns in my image so you can see that in addition to returning two rows for partition number 1 of index 1 on both object_id 13 and 15, the metrics for these two index partitions also differ.
I can also replicate the issue when filtering the results of the function to all rows in the specific database:
SELECT *
FROM sys.[dm_db_index_operational_stats](99, NULL, NULL, NULL)
ORDER BY database_id, OBJECT_ID, index_id, partition_number
It’s not until I specify the object_id that the results appear to aggregate or disregard the mutliple row results.
SELECT *
FROM sys.[dm_db_index_operational_stats](99, 13, NULL, NULL)
ORDER BY database_id, OBJECT_ID, index_id, partition_number
This is also occuring with one additional database on the instance, and is occuring for both object_id 13 and 15 as well. These databases do share similarities. They were both recently added to the instance – migrated from a SQL 2000 instance and upgraded for the SQL 2005 instance, but still running in 8.0 compatibility mode. These objects, it should also be noted, are system objects: object_id 13 is the syshobtcolumns table and object_15 is the syshobts table. Both these objects are hidden from the end user. Both store information, as the names suggest, concerning hobts (Heaps Or B-Tree) objects in your SQL instance. For further clarification on the nature of hobts (pronounced hobbits) I leave that to Aaron Alton (aka Arron the Hobt, as well as Kalen Delaney and Paul Randal (neither of which are from Middle Earth, though Paul has the accent down pat.)
Kalen Delaney, SQL Server Magazine, Managing Data Space (June 2006)
Paul Randal, SQL Server Storage Engine Blog, Under the Covers: I AM Chains and Allocation Units in SQL Server 2005 (unfortunately it appears this link may no longer be available, but Michelle Ufford aka @SQLFool on Twitter, makes mention of the contents of that post on SQLServerPedia.)
Since I have no desire to attempt to performance tune either of these system tables I will need to either do a check to exclude these tables from the collection process I perform or I will need to remove the primary key constraint from my persisting table and allow duplicate records. I am not happy with either option as it adds another layer of complexity into my solution and it also allows for mis-represented data in the results set. I can not truly call this a bug with collection of index operational data via the DMF when the source db is running in 8.0 compatibility, as I have other databases in the same compatibility mode on this instance and have not encountered the situation. The two databases in question both were restored from the same SQL 2000 instance and they serve data to the same turnkey application so structural/logical structures could be the same and therefore result in both databases presenting this to my tool.
What I am more concerned about though is that the sys.dm_db_index_operational_stats DMF is returning different results for the same object depending upon whether you filter the parameters. Considering the underlying rows should be unique, this should never be an issue.