Retaining historical index usage statistics for SQL Server Part 2 of 3

By: Tim Ford   |   Updated: 2009-06-05   |   Comments (3)   |   Related: 1 | 2 | 3 | > Indexing

Problem

In part one of this series of persisting Dynamic Management View data that is stored in the cache and sourced from the Query Optimizer we examined the process for persisting data for the sys.dm_dm_index_usage_stats DMV.  That DMV stores usage metrics (as the name implies) for all the indexes on a Microsoft SQL Server instance.  Welcome to part two, in which we look at performing the same task for the sys.dm_db_index_operational_stats Dynamic Management Function (or DMF.)  The process is identical, the code is however quite different due to the structural differences between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.  To recap why we would want to do this I suggest reviewing part one of the series.  In short, we do so because the information is cached and whenever the SQL Server service is restarted that information, which is extremely valuable for performance tuning as well as diagnosing locking and blocking issues over time, is lost forever.

Solution

If you have been following along with this series you will have already created a dedicated database for the objects we'll be creating in this tip.  The database is similar to one I use on all my supported SQL Server instances for storing metadata for various administrative purposes from automated job code executed via SQL Server Agent, functions and stored procedures used for diagnosing performance and space consumption, and of course, persisted data from various Dynamic Management Objects such as what we're examining in this series.  However, for those of you just joining us in this tip, then you'll need to run the CREATE DATABASE Transact-SQL statement below as well as the code required to create the MetaBot schema that owns the objects in this series. 

Step One:  Create the Administrative Database and Necessary Supporting Objects

I separate my database objects within my administrative database by schema.  You'll see below that after creation of the database, I create a MetaBot schema that will serve as the schema for all objects of this process type.

From here we only require two user-created database objects:  the repository table that will persist the data (MetaBot.dm_db_index_operational_stats) and the stored procedure used to collect it. 

The table is (for the most part) a duplicate in structure of the sys.dm_db_index_operational_stats Dynamic Management Function with a few notable exceptions:

• collection of columns prefixed with "last_poll_"
• date_stamp column

The last_poll_... columns are vital for the calculations that go into the incrementing and updating of usage counts each time the stored procedure will run against sys.dm_db_index_operational_stats.  If your instance never is subject to a service restart these columns would not be necessary, however we all know that is not the case.  Even the best-constructed environments must still have routine maintenance that will require bringing the system down.  The date_stamp column will record when the last refresh of MetaBot.dm_db_index_operational_stats occurred.  It also serves to determine how the usage values are incremented in that it is compared to the last time the services were cycled.  Depending on whether that occurred pre or post the last refresh occurred, a different calculation is used to update the related columns.  This process is identical to what I outlined for the sys.dm_db_index_usage_stats DMV, taking the structural changes (far more columns; none of which are date-based) into consideration. 

The code is comprised of a few different logical sections:

• Variable declarations and value assignments
• Update of existing values with new usage counts.  The two variables in the stored procedure determine which UPDATE script runs in the confines of the stored procedure:
  • If a restart of services has not occurred since the last collection of metadata the formula for updating the relevant column with new data is: MDDIOS.COL + (DMF.COL - MDDIOS.LAST_POL_COL) Simply put, it's the currently stored value for the column (MDDIOS.COL) + the difference between the current value of the corresponding column in the DMF (DMF.COL) and the last value persisted from the DMF (MDDIOS.LAST_POL_COL).
  • If a restart of services has occurred since the last data collection the formula used will be: MDDIOS.COL + DMF.COL, since the values stored in the Dynamic Management Function will have reset upon service restart.
• Insertion of new rows associated with indexes that have, up to this point, not been used in any read or write processes.

When a unique record is first encountered it is inserted into the persisted table.  A unique record is based upon the combination key of database_id, object_id, index_id, and partition_number in the case of the sys.dm_db_index_operational_stats DMV.  If this combination of identifiers already exists in the persisted table, the stored procedure relies upon the underlying determination of whether or not a system restart has been encountered since the last time the metadata was persisted.  If it has not, then the usage columns are incremented by adding the existing value of that field to the difference between the new value from the DMV and the last value that was read from the DMV when the last run of the stored procedure executed.  If a restart did occur, then the counters are all reset to 0.  This means that the formula previously outlined would result in incorrect numbers being persisted.  Instead you must take the sum of the existing column value and the corresponding value from the DMV as the new value to be persisted.  In all cases the calculation for the last_poll column value will remain unchanged as will the code associated with the INSERT of new index usage rows.  Just as with sys.dm_db_index_usage_stats, you may notice that we do risk the loss of counter data.  The only way to ensure that all usage data is persisted would be if this stored procedure would run prior to any service restart - not a viable option.  However, unlike user data, we can live with data loss in this case.  We are using this metadata for analyzing locking, blocking, and detailed use of the indexes over a period of time for the purposes of tuning our indexing structure of the databases on the instance.  So long as we record significant data over the lifetime of the instance we have no need to capture all usage values.

Just as with sys.dm_db_index_usage_stats, the final step is to create a SQL Server Agent Job for the execution of this stored procedure.  I'll leave that up to you.  In our environment I run this process six times daily.  It's very lightweight and I've not found it to interfere with normal user operations or backup cycles.  I do not run it during the timeframe of scheduled index maintenance, but that is by choice on my part.  I encourage you to test with schedules that fit your individual needs.

In the next tip of the series we will create a similar process for the missing index Dynamic Management Views and Functions; these require different handling since their structure is so much different than the two DMOs we've studied so far in this series.

Next Steps

• Follow this series from the beginning.  Here is the first part of the series, pertaining to sys.dm_db_index_usage_stats.
• Review tips on utilizing the results from sys.dm_db_index_operational_stats on MSSQLTips.com
• Want a complete list of Dynamic Management Objects? Click on the link!
• Learn more about indexes courtesy of MSSQLTips.com
• Create the objects and structures from this tip in your environment.
• Download the source code
• Read Part1, Part3

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips