DMV Queries Tied to My Custom Index Stats Tables

I've mentioned a set of DMV queries that I use in a previous post on this blog. I've been chopping and changing the base set of queries for quite a while to suit my needs.

I have also written a series of posts describing how to perform index and statistics maintenance on a SQL Server's databases without losing the very important index stats that the queries mentioned above return.

So, in this post I will show you how I've modified a few of the index focused DMV queries to include the maintenance tables where I now store the cumulative index statistics.

Defragging Indexes Without Losing Index Stats - Part 5

The stored procedure

/*********************************************************************************
UPDATE: Bug fixes and increased FILLFACTOR control!
*********************************************************************************/
The first 4 posts of this series covered the concepts of recording index usage statistics for performance tuning while continuing to maintain a database’s tables and indexes. They also presented the individual SQL scripts to perform the various parts of that process.  The most recent post put all of those scripts together into a single set of queries that could be run on a single database.

Most of us, however, have more than one database on a SQL Server instance. Therefore, deploying that last script on a large number of databases is a bit messy. Any modifications will be difficult to manage.

In this post I will present a sample execute statement to run the stored procedure. It is designed to be run centrally, and will loop through all the databases in the instance (there is a parameter to include/exclude the system databases). I deployed the SP onto my Admin_DB where I also store the tables that keep the index usage statistics and table maintenance history.