Geeks With Blogs
Evan Koch Musings on BizTalk Server and SQL Server

In SQL Server there are generally two types of fragmentation that can plague a database: internal fragmentation and external fragmentation.  Both of these types of fragmentation can cause the hard drive to spend more time searching for data on the disk, and since the hard drive is the slowest component in a system, this is something we’d like to speed up if possible.

Internal fragmentation comes about as records are added, deleted, or moved within a database file.  As records are deleted from a table, space within an extent frees up and it can later be claimed when a new record is added.  For an index this type of fragmentation occurs when the logical ordering of the data does not match the physical ordering of data on a disk; ideally these two should match each other.

External fragmentation can occur when SQL Server requests space on the hard drive be allocated for a data file and the OS returns non-contiguous blocks.  The more that the access arm within a hard drive has to move around to seek data, the slower the response time will be for any operations that use that data if it’s not in the cache.  To help reduce the likelihood of this, try to defragment the drive on which the data will reside before allocating the data files, and when allocating the files, calculating the space needed and adding in some room for growth.  This could also make it less likely that SQL needs to request more space for a database during business hours and affect database performance as the space is being allocated.

Another thing to consider in terms of growing indexes is pages splits.  As an index grows, if a new row is added to a full index page, SQL moves half of the rows in the existing page to a new page to make room.  This can cause SQL to request more space, which can degrade performance.  In order to offset the likelihood of this occurring you can specify the FILLFACTOR value when building or rebuilding an index.  Specifying a FILLFACTOR value of 70 will fill only 70% of the leaf pages with data and leave the remaining 30% for future data so that SQL won’t need to allocate more pages as new entries are added to the index.

It’s typical to have some form of maintenance plan that executes daily and performs upkeep tasks on the indexes.  In databases I’ve written in the past, the maintenance plan ran DBCC INDEXDEFRAG Monday through Saturday and rebuilt the indexes on Sunday when it was less likely that the rebuilding would interfere with any users.  It’s also worth pointing out that while rebuilding an index, the underlying table is unavailable while the process is being performed; by contrast, the underlying tables can be accessed while an index is being defragmented.

Posted on Sunday, August 26, 2007 8:10 AM | Back to top

Comments on this post: Index Maintenance

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Evan Koch | Powered by: