Automating Index Defragmentation in MS SQL Server Database

Preface

The World Wide Web offers a bunch of information on SQL Server index defragmentation or SQL Server  index rebuild. However, most of the recommendations refer to databases that have minimum load time (mostly, at night).

And what about databases that are used for both, data modification, and retrieving information on a 24/7 basis?

In this article, I will provide a mechanism for automating SQL Server index defragmentation implemented in a database used in the company I work for. This mechanism allows defragmenting required indexes on a regular basis since index fragmentation takes place constantly in the 24/7 system. Often, this is not enough to perform index defragmentation once a day.

Solution

Firstly, let’s take a look at the general approach:

  1. Creating a view showing which indexes have been fragmented and the percentage of the fragmented indexes.
  2. Creating a table for storing index defragmentation results.
  3. Creating a stored procedure for analyzing and defragmenting the selected index.
  4. Creating a view for viewing statistics of the index defragmentation results.
  5. Creating a task in Agent for running the implemented stored procedure.

And now, let’s take a look at the implementation:

1. Creating a view showing which indexes have been fragmented and the percentage of the fragmented indexes:

This view shows only indexes with the fragmentation percentage greater than 30, i.e. indexes that require defragmentation. It shows only indexes that are not heaps, since the latter ones may lead to negative effects, like blocking of such a heap, or further index fragmentation.

The view uses the important system view sys.dm_db_index_physical_stats.

2. Creating a table for storing the index defrag results:

The most important thing about this table is keeping data deletion in mind (for instance, data that is older than 1 month).

Table fields will become understandable from the next point.

3. Creating a stored procedure for analyzing and defragmenting the selected index:

4. Creating a view for viewing the statistics of the index defragmentation results:

This view can be used to notify administrators daily about the results of the index defragmentation automation.

5. Creating a task in Agent for running the implemented stored procedure

Here, we need to pick the time in an experimental way. In my case, somewhere I got 5 minutes, somewhere – 1 hour.

This algorithm can be expanded on several databases, but in this case, we need an additional point 6:

Gathering all the statistics of the index defragmentation automation in one place for subsequent sending to administrators.

And now, I would like to dwell on the already provided recommendations for index support:

  1. Simultaneous defragmentation of all indexes during the minimal database load is unacceptable for the 24/7 systems, since indexes are constantly fragmented and there is almost no time when database remains idle.
  2. SQL Server index reorganization – this operation blocks a table or partition (in the case of a partitioned index), which is not good for the 24/7 systems. Then, index rebuild in the real-time mode is supported only in the Enterprise solution, and may also lead to data damaging.

This method is not optimal, but it can successfully cope with ensuring that indexes are properly defragmented (not exceeding 30-40% of fragmentation) for their subsequent usage by the optimizer for building execution plans.

I will be grateful for your comments with reasoned pros and cons of this approach, as well as for the tested alternate suggestions.

References:
Reorganize and Rebuild Indexes
sys.dm_db_index_physical_stats

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.