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

Problem

In this, the third of the four part series on persisting the rowset results from the indexing Dynamic Management Views cached in temporary internal SQL Server structures we will explore what is required to store the missing index-related metadata.  Part one of this series examined the process for persisting data for sys.dm_dm_index_usage_stats; the DMV pertaining to usage metrics for all the indexes on a Microsoft SQL Server instance.  Part two centered on constructing a similar process for the sys.dm_db_index_operational_stats Dynamic Management Function.  I suggest reviewing these precursor tips prior to tackling this one.  As with the previous tips, the reason I perform these processes is to protect valuable index tuning information from being lost if the SQL Server instance is restarted.  If that occurs, this information is lost; gone with it is important historical usage, structural, and operation information that can be used to tweak existing indexes or create new ones that improve performance.

Solution

As with the previous tips in this series we’ll be working with the dedicated database and schema set aside for these structures.  I’m including the code to create the database once again here, but it is identical to what was presented earlier.  I use a dedicated schema for the processes that collect this information in my environment.  If you prefer to simply use the dbo schema you may do so with a simple find and replace process in the code that follows.

Step One:  Create the Administrative Database and Necessary Supporting Objects

At this point, we need to create three tables to persist the data and a stored procedure to collect the rowsets from each Dynamic Management View.  The code below creates tables for rowset data from:

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_columns 

You may notice that there is no corresponding structure for persisting sys.dm_db_missing_index_columns.  That is because this object is a Dynamic Management Function whose results are not affected by a service restart.  It will use the information persisted in the four tables created here to return results.  Let’s take a look at the code to create the tables:

As with the previously presented tips on the matter, the tables created here are quite similar in structure to the source DMVs.  The exceptions are additions of columns starting with “last_poll_” for any columns that store running total values.  These will aid in accounting for different methods of incrementing values depending upon whether a service restart has been encountered since the last time the data has been collected from the DMVs for physical storage.  There is also a column named “date_stamp” to record the last time the data was pulled from the DMVs.  The last deviation is the addition of the index_handle column in MetaBOT.dm_db_missing_index_columns.  Since the source is a DMF that accepts index_handle as a parameter (from sys.dm_db_missing_index_details) and does not produce it as an retuned column, I did have to add the column to the persisted table, and then populate the value (as you will see in the stored procedure code) from a CROSS APPLY between sys.dm_db_missing_index_details and sys.dm_db_missing_index_columns.

At this point we’re prepared to outline the code for collecting this information.  The code will first insert new records into MetaBOT.dm_db_missing_index_groups, MetaBOT.dm_db_missing_index_details, and MetaBOT.dm_db_missing_index_columns.  There is no need to update existing records in either of these tables, as they do not store any incremental/running total values.  They simply store indentifying columns that reference running total columns in sys.dm_db_missing_index_group_stats.  After these records are inserted, the last system restart date (as calculated by the creation date of tempdb, which is recreated upon each SQL Server service start) is compared to the last time the data was persisted from the missing index DMVs, as is available from the date_stamp column in MetaBOT.dm_db_missing_index_group_stats.  Depending upon whether the last restart date took place before or after the last time data was collected, the procedure code will properly calculate and persist the running total values from the sys.dm_db_missing_index_group_stats DMV along with indentifying columns and a new (current) date_stamp value.  Finally, any records that exist in the DMV, but are not yet recorded in the physical table, are inserted via the final DML INSERT statement.

As with the methods outlined previously, the calculations for updating running total values is as follows:

• 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: TABLE.COL + (DMV.COL – TABLE.LAST_POLL_COL) Simply put, it’s the currently stored value for the column (TABLE.COL) + the difference between the current value of the corresponding column in the DMV (DMV.COL) and the last value persisted from the DMV (TABLE.LAST_POLL_COL).
• If a restart of services has occurred since the last data collection the formula used will be: TABLE.COL + DMV.COL, since the values stored in the Dynamic Management View will have reset upon service restart.

Just as with sys.dm_db_index_usage_stats, and sys.dm_db_index_operational_stats, the final step is to create a SQL Server Agent Job for the execution of this stored procedure.  In our environment I run this process six times daily, immediately after I’ve run the similar Agent jobs to store data from sys.dm_db_index_usage_stats, and sys.dm_db_index_operational_stats.  It runs outside of the timeframe of scheduled index maintenance.  I encourage you to test with schedules that fit your individual needs.

The final tip in this series will outline methods for using this information as opposed to relying on the Dynamic Management Objects after a system restart occurs.  The need to persist this data will be quite clear once we have a chance to review the query results in this final tip in the 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.  Here is the second tip in the series.
• Review tips on utilizing the results from the missing index Dynamic Management Functions on MSSQLTips.com
• Check out our list of Dynamic Management Objects.
• Create the objects and structures from this tip in your environment.
• Download the scripts
• Read Part1, Part2