Defragging Indexes Without Losing Index Stats - Part 3

In Part 1 and Part 2 of this series I discussed synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine and the script for saving a database's index usage statistics. This part of the series will go into the query that both identifies the indexes that are fragmented and dynamically generates the defrag scripts.

Determining which indexes need attention

The script below interrogates the sys.dm_db_index_physical_stats DMV to get the percent fragmentation of each index on each user table. By setting the fragmentation thresholds you determine at what point the indexes of a table will be left alone, reorganized or rebuilt. The lower the thresholds the more often the indexes will be maintained. I ignore the indexes on any table that does not have a clustered index (is a heap) as such indexes cannot be properly defragged so it is a waste of time and resource to do so. My opinion is that all tables should have a clustered index.

As I mentioned in previous posts, there are already quite a few comprehensive scripts out there that do what mine does (some are definitely better, for now at least!). So, at the risk of reinventing the wheel, but with the benefit of adding some specific flexibility that I wanted, I’ve gone ahead and written my own defrag script.

The queries below also create the appropriate ALTER INDEX statements for each index:

-          Ignoring indexes on READONLY filegroups

-          Forcing REBUILD with OFFLINE mode for indexes that do not allow page locks

-          Forcing OFFLINE mode for indexes that have LOB or FILESTREAM columns

-          Only defragging partitions that meet the threshold criteria for partitioned tables

-          Removing FILLFACTOR hint for partitions

Amendments that still could be made:

-          Adding variable to choose all partitions or individual partition defrag

-          Allowing for variable fillfactor settings (based on collected statistics)

-          Much more, I’m sure

So here is the script:


Please come back soon for the next part of my series and read more about my how I'm building my entire procedure. But if you're anything like me, impatient, a preview of the stored procedure that defrags and updates stats for all the databases on a server is free to download from my Google Drive.
CAVEAT: It has been tested a bit, but I offer no guarantees and the code is provided as is.