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.


CHANGES:

• Bug fix: Index usage stats table was being deleted - now fixed
• Fillfactor: I've added a new  parameter: @FillFactorOverride. There are three options
• 0 - Rebuilds the indexes using the existing fillfactor
• 1 - Overrides existing fillfactor with the @FillFactor value set for the stored procedure 
• 2 - If an index's fillfactor is 0 then @FillFactor will be used else the index's existing fillfactor will be used.

I made this change to increase flexibility and control over fillfactor as a result of performance tuning I was carrying out around page splits. It's obvious that, depending on the nature of the columns in an index, they will require different levels of fillfactor. And, for OLTP databases, I believe that, in most cases, a default fill factor of around 90 is advisable, it is not a hard rule. And, from the beginning, it has been an aim of mine to get this script to allow for better management of fillfactor. So, although not perfect, I think this is a step in the right direction. 

Obviously, there are quite a few things that could be added and improved. The main things that I am aware of are: 

•  Dynamically change the fillfactor of indexes based on historic fragmentation levels. To me this is, not quite a holy grail, but a real goal. I want to record an average fragmentation level and based on that dynamically increase or decrease the fillfactor of each index. (Although, now that I've added the @FillFactorOverride option, I'm not sure I will focus on this anymore) 
• Recording index usage that hasn’t changed since the last run of the SP. This is a small bug in my script. In certain cases the section of the stored procedure that records the index usage stats will add to the existing count in my history table rather than replace the number, resulting in a multiple of the actual read/write stats for the index.

Any suggestions for improvement are welcome.

The stored procedure (download) can be run with the following parameter settings: