The Accidental DBA (Day 14 of 30): Index Maintenance

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

Like a high performance sports car, SQL Server databases require routine maintenance to maintain their best performance. Unless a database is read-only, one of the most common maintenance tasks that is required periodically is rebuilding or reorganizing the indexes in the database to remove fragmentation. Index fragmentation generally is categorized as one of the following two problems:

  • Logical scan fragmentation – physical page ordering not matching the index key order, due to page splits occurring during INSERT/UPDATE operations. This affects the performance of range scan operations and results in an increased number of smaller block I/O operations, reading as little as a single 8-KB page at a time.
  • Low page density – individual pages within the index have large amounts of free space due to page splits during INSERT/UPDATE operations or DELETE operations removing data from the table.

There are many ways to deal with index fragmentation that can be automated in SQL Server.  Probably the most common method implemented by Accidental DBAs is the use of the built in Database Maintenance Plans in SQL Server using a plan built through the Maintenance Plan Wizard. However, other options exist that are better, using custom T-SQL scripts that do less work by first analyzing the index fragmentation and page density levels before determining the best option for reducing the fragmentation with the least amount of work.

Database Maintenance Plans

Database Maintenance Plans exist in SQL Server to simplify the tasks required for general maintenance of the databases within an instance, and are generally very easy to configure.  However, using the Maintenance Plan Wizard can lead to redundant tasks for index maintenance, and the tasks are executed against all of the indexes, regardless of whether fragmentation exists or not.  Common Maintenance Plans that I’ve seen when consulting have a Reorganize Index Task that is immediately followed by a Rebuild Index Task in the same schedule, since this is what the default options for the wizard will allow you to configure unless changes are made. While something is better than nothing, this unfortunately also performs double the work by first reorganizing the indexes, which removes the fragmentation but may not be the most efficient method of doing so, and then rebuilding the index, which creates a copy of the index that does not contain fragmentation before dropping the original page allocations for the index.

The Reorganize Index Task is best suited for indexes with low fragmentation levels whereas the Rebuild Index Task is best suited for indexes with high fragmentation.  However, the problem with both of these maintenance plan tasks is that neither of them actually check the fragmentation level before running the ALTER INDEX operation against the indexes.  If the task is configured to run for All Databases, which is very common for most maintenance plan usage, then the task will reorganize or rebuild all of the indexes in all of the databases, even if the indexes are not actually fragmented.  For the rebuild task, this can result in high I/O activity and transaction log generation that could be avoided through analysis-based index maintenance using T-SQL scripts.

Custom Scripts

The preferred method of performing index maintenance with the least amount of overhead involves first analyzing the index fragmentation levels and then deciding whether it is more efficient to reorganize the index using ALTER INDEX … REORGANIZE, or to rebuild the index using ALTER INDEX … REBUILD.  The index fragmentation level can be found using the sys.dm_db_index_physical_stats function in SQL Server, and the general recommendations in the Books Online are:

  • If an index has less than 1,000 pages, don’t bother removing fragmentation
  • If the index has:
    • less than 10% logical fragmentation, don’t do anything
    • between 10% and 30% logical fragmentation, reorganize it (using ALTER INDEX … REORGANIZE)
    • more than 30% logical fragmentation, rebuild it (using ALTER INDEX … REBUILD)

Since this type of index maintenance requires custom coding it is not quite as easy to implement as the built in Maintenance Plans, it might seem like more work than it’s worth.  However, there are a number of freely available scripts that are easy to deploy that automate all of this for you, and can even be faster to implement than manually building a Maintenance Plan using the Maintenance Plan Wizard.

In a recent survey by Paul, Easy automation of SQL Server database maintenance, more than 40% of the almost 500 respondents use some or all of Ola Hallengren’s Maintenance Solution, and I always recommend these scripts to clients as a way to save them consulting time.  Ola provides an easy-to-deploy alternative to Database Maintenance Plans in a single script that creates all the customized stored procedures and SQL Agent jobs for index maintenance, statistics updates, consistency checks, and even database backups that follow recommended best practices for SQL Server.  I replaced my own scripts as a DBA close to five years ago with Ola’s scripts when they were first available and have been using them for SQL Server maintenance tasks ever since.  Paul’s blog post also points out other popular scripts from SQL Server MVPs Michelle Ufford and Tara Kizer as other options specifically for index maintenance.

Summary

Proper index maintenance is critical task for maintaining the performance of SQL Server databases, and while there are multiple options for automating this common task, custom scripts provide the best method of minimizing the work to be done by performing the most efficient operation based on the actual fragmentation that exists.

Our online training (Pluralsight) courses that can help you with this topic:

7 thoughts on “The Accidental DBA (Day 14 of 30): Index Maintenance

    1. Hey Dinesh,

      Online index operations are an Enterprise only feature and require the use of the ONLINE=ON syntax in the WITH clause for the ALTER INDEX statement. If you use Database Maintenance Plans there is a check box option for ONLINE index operations, or if you look at Ola’s scripts there is an option for the stored procedure to specify whether or not to use ONLINE=ON.

      Cheers!

  1. Thanks for the article, very informative and useful

    I was wondering, whats the reason for “If an index has less than 1,000 pages, don’t bother removing fragmentation”…. because in my case…. it takes almost 30 min to run update stats on couple of indexes with page count less then 100 and with fragmentation more than 30%… do I need to increase my limit to 1000?

    1. The reason is that mixed extent allocations on smaller indexes can often lead to high fragmentation numbers that you can’t eliminate and if an index is 1000 pages or less and typically in memory the fragmentation of that index won’t matter performance wise, because it won’t affect read ahead I/O operations physically, the pages are already in memory it is just logical I/O. See http://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.