SQL Server 2005 Index Best Practices

Problem

In continuing with our series of tips on Best Practices for SQL Server I’m turning my sights on Maintenance.  Specifically in this tip we will be discussing Index maintenance: when, if, why, and how are questions that will be addressed.  Many tips here at MSSQLTips.com are devoted to just this topic and most of the detailed steps on how to perform index maintenance are going to be links to tips we’ve previously published.  I will also not be presenting what index fragmentation is.  If you are looking for information on either topic please refer to the Next Steps section below.  What we will focus on is the Best Practices associated with indexing maintenance as a whole.  With that understanding, let’s proceed!

Solution

As a SQL Server DBA since the waning days of Microsoft SQL Server 6.5 I’ve been exposed to a wide assortment of SQL instances of not only my creation, but from developers and vendors working without the oversight of a qualified Database Administrator.  I’ve read through more database installation documents than I could possibly quantify and I’ve seen some requirements that would make even the coolest lose their composure.  Some of the issues I’ve run across are the direct blame of ignorance, laziness, oversight, or hubris on the part of the individual with their hands on the keyboard or vendor developing the requirements document.  In a few cases I have to lay the blame at the feet of Microsoft themselves, though only in-so-far that their design has given false impressions as to what the standards are or should be in terms of maintenance.  This last comment should be explained by a true story that I played a role in not too long ago.

I installed a database required as the data repository for an application our company purchased from a vendor we’ve purchased many such products from in the past.  The SQL requirements questionnaire we had the vendor complete before installation stated that the product could be hosted on a shared environment – meaning we did not need to dedicate a SQL Server instance strictly for this single database.  We made a home for it on one of the nodes of our enterprise cluster without any issues and then went about our business.  About a month later the users complained of some issue (it’s not pertinent to the discussion here so I will not be delving further) that they were concerned may have been caused by a database issue.  The vendor’s support department was contacted and in turn I was engaged to answer a few questions about the SQL environment.  The vendor wanted to be assured that I was performing index maintenance once a week.  I informed them that I was not, but that I was performing this task nightly, where required to keep index fragmentation in check.  It was at this point when one of the most absurd statements about standards and best practices was ever made to me.  The tech support individual I spoke with, who just happened to be the vendor’s DBA, stated that was unacceptable and that I need to perform this task weekly, on Sundays at 1:00 am, just as Microsoft requires.  If that sounds vaguely familiar to you it is because that is the default value for the schedule associated with the Reorganize data and index pages action in the original SQL 7.0 and SQL 2000 Maintenance Plans.  The vendor “Expert” was citing this metric as the standard set forth by Microsoft for when index maintenance should be performed and how frequently it should take place.

So please fellow SQL Server Professionals, do not rely on the Maintenance Plans in SQL Server.  They are adequate for a small company who may not have a dedicated IT department or for a home installation of SQL Server.  Any scheduled maintenance is better than none at all.  If you’re taking the time to read this however, then you most-likely are in a position or in an organization that has outgrown the usefulness of Maintenance Plans. 

So when do you remediate your index fragmentation?  Like I mentioned above, you do so when the need arises, but on a scheduled basis and off hours if possible.  I have a SQL Agent job that runs against each of the databases on each of my instances once per day.  It identifies which indexes are fragmented over 15%.  It then rebuilds those that are encountering fragmentation in excess of 30% and reorganizes those with fragmentation between 15%-30%.  It disregards any small indexes (less than 30 pages).  How do I accomplish this?  Primarily through the sys.dm_db_index_physical_stats Dynamic Management Function.  What follows is a simple query that I use to identify those indexes that meet the criteria I just spelled out for the current database:

The sys.dm_db_index_physical_stats DMF accepts 5 parameters: database_id, object_id, index_id, partition_number, and mode.  I limited the results to just the current database (courtesy of the DB_ID() function.  I then filtered the results to just those indexes whose fragmentation met the limits I was interested in and whose size were greater than 30 pages.  Furthermore, I excluded heaps (tables without clustered indexes) from the result set.  I only showed you this query so that the examples in the links below made more sense.  For you will see that there are many options available for creating your own index maintenance scripts, yet the core functionality comes down to querying sys.dm_db_index_physical_stats, making note of which indexes meet the criteria for your index fragmentation watch list, and then generating and executing ad-hoc ALTER INDEX statements for rebuilding or reorganizing your indexes accordingly.

In terms of distilling the process of index maintenance down to a list of best practices this is what I recommend:

• On a scheduled basis – daily being my recommendation – identify which indexes in your environment are fragmented beyond an acceptable measure as it conforms to your environment.  No not wait for a week in order to ascertain when your indexes require maintenance.  By that point your users could have endured six days of suffering with poor performance as a result of a poor physical state of affairs with your indexes.
  • An acceptable starting point would be:
    • greater than 30% average fragmentation is a candidate for rebuilding the index
    • 15% – 30% reorganize the index
    • ignore smaller indexes.  I’ve seen this exclusion level anywhere between 10 – 100 pages, but I tend to lean towards 30 pages as my cut-off point.
• Either store the results to a table and use ad-hoc T/SQL to create ALTER INDEX statements to run at a predetermined time for each database or run the ad-hoc statements you generate for all databases at a predetermined time when user impact is low.
• Record the results to a table with a date stamp to allow you to query the results to determine if specific indexes are fragmenting at an undesirable frequency.  This may lead you to make changes to your fill factor for the indexes in question in order to reduce page splits and fragmentation.
• Performing index maintenance against a database in Full recovery mode will impact the transaction log for the database.  Pay attention to the effects of indexing maintenance against such databases and increase the frequency or alter the schedule of your log backup accordingly to keep your transaction logs to a manageable size for your instance.

Please review the Next Steps section for code associated with performing index fragmentation remediation.

Next Steps

• What is index fragmentation
  • SQL Server 2000 to 2005 Crosswalk
  • Fragmentation Station #4: Fragmentation and How to Avoid It
• How do you resolve index fragmentation
  • Fixing Index Fragmentation in SQL Server 2005 and 2008
• Related indexing tips from MSSQLTips.com
  • SQL Server Indexing Checklist
  • SQL Server Indexing Basics
• Read more tips by the author here.
• Future tips on the subject by the author are on the way.  Stay Tuned for tips on:
  • Logging index fragmentation history to a table for reporting and assessment
  • Running index maintenance against multiple databases at a time
  • Scheduling index maintenance on your SQL Servers