SQL Server 2005 Index Best Practices

By:   |   Comments (9)   |   Related: More > DBA 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:

SELECT DB_NAME(SDDIPS.[database_id]AS [database_name]
        
OBJECT_NAME(SDDIPS.[object_id]DB_ID()) AS [object_name]
        
SSI.[name] AS [index_name]SDDIPS.partition_number
        
SDDIPS.index_type_descSDDIPS.alloc_unit_type_desc
        
SDDIPS.[avg_fragmentation_in_percent]SDDIPS.[page_count] 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'detailed'SDDIPS 
        
INNER JOIN sys.sysindexes SSI 
                
ON SDDIPS.OBJECT_ID SSI.id 
                        
AND SDDIPS.index_id SSI.indid 
WHERE SDDIPS.page_count 30 
        
AND avg_fragmentation_in_percent 15 
        
AND index_type_desc <> 'HEAP' 
ORDER BY OBJECT_NAME(SDDIPS.[object_id]DB_ID()), index_id

01

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, January 31, 2013 - 7:51:56 AM - ferlawy Back To Top (21831)

trying replacing DB_ID(), with the actual database ID from sys.databases.

 

or begin the script by adding

 

Declare @DBID Int

Set @DBID = DB_ID()

 

then go ahead to replace DB_ID() in the script with the @DBID.

 


Monday, July 2, 2012 - 10:59:48 PM - parikshit Back To Top (18313)

went through ur code ...bt  i came to know that the code was not meant to BEGINNERS ..i am beginner..


Wednesday, October 7, 2009 - 11:16:17 AM - admin Back To Top (4152)

The OBJECT_NAME with the database_id is new with SQL Server 2005 SP2. 

Also, using this DMV to get index stats is new for SQL 2005 as well.

Take a look at these other tips to get fragmentation information for SQL 2000.

http://www.mssqltips.com/tip.asp?tip=1014

http://www.mssqltips.com/tip.asp?tip=1165


Wednesday, October 7, 2009 - 11:10:47 AM - peteo Back To Top (4151)

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


Tuesday, October 6, 2009 - 8:50:26 AM - dejansen Back To Top (4144)

 If running index maintenance on SQL Server 2005 with large databases, avoid using sys.db_db_index_physical_stats to generate fragmentation stats. That DMV is notoriously poor performing and MS has no plans to fix it in SQL Server 2005. It performs fine in SQL Server 2008. A better alternative is to use DBCC SHOWCONTIG to generate fragmentation stats. Yes, it will soon be deprecated (after SQL Server 2008), but I'm talking specifically about SQL Server 2005. For a sample defragmentation script, email me at david dot jansen at gmail dot com.


Tuesday, October 6, 2009 - 8:33:57 AM - seecoolguy Back To Top (4143)
Why do you choose such a low number of pages? I gauged by Microsoft's article of only defragging for Over 1000 pages. Whenever I do less I end up with odd errors where I need to drop an index completely and then rebuild it with a new "Create Index" statement.

Tuesday, October 6, 2009 - 8:07:46 AM - admin Back To Top (4140)

What version of SQL Server are you running?

You can run the below to get this.

SELECT @@Version


Tuesday, October 6, 2009 - 6:08:35 AM - peteo Back To Top (4138)

Like the article. Tried the code and got this error.

 

Msg 174, Level 15, State 1, Line 2

The object_name function requires 1 arguments.

 

I simply copied the code but I am sure it is something stupid I am doing. Any clues?


Tuesday, October 6, 2009 - 6:08:02 AM - DavidB Back To Top (4137)

Thanks for your article and for your script on identifying indexes to be de-fragmented. I too have a custom job that only rebuilds indexes when fragmentation reaches a specified amount. However, I only use this script as an exception instead of as a general rule. If a database is large and a full database de-fragmentation will take a while, I would use this script. Otherwise, I use maintenance plans. For system databases, I cannot see a reason for using a script instead of maintenance plans. This is just my opinion and which ever works best in your environment is your decision.

 

Your experience with database support from vendors is not unusual. Sometimes their recommendation only make sense if your running a DBMS other than SQL Sever or maybe running an older release of SQL Server. Their quoting of suggestions like its scripter is laughable at times but sometimes they are right because of some nuance.

 

A DBA must think for them self at the same time be opened minded to tips provided by other DBA’s through articles and other publications. This article has motivated me to re-examine a few databases to see if a nightly conditional reorg on a few tables might help with some locking issues. Thank You

 

 















get free sql tips
agree to terms