Manage Learn to apply best practices and optimize your operations.

Managing index fragmentation in SQL Server

As a database is used and pages are split, any index of that data becomes fragmented to a degree. The greater the fragmentation, the greater your performance overhead.

Database indices are a key determinant in your database performance. Every time you run a query, create a view or cursor or retrieve a dataset in some way, you are hitting up against indices. Put aside the instances where you are building indices on the fly; all of the longest-running database operations are probably supported in a well-formed database application by maintained indexes.

As a database is used and pages are split, any index of that data becomes fragmented to a degree. Fragmentation therefore adds overhead into your system when the database is forced to use multiple entries in the index to access that same page. Your overhead is particularly troublesome when you depend upon fast performance from a central read-only table, and less so when you have a dynamically changing table where new row data can be added without having to split an excessive amount of pages. Therefore, the amount of index fragmentation you might want to allow depends upon the type of table data you are supporting and how the index is used. That's the reason why there is no standard fragmentation value applicable to SQL Server database indexes, and why there is an adjustment factor that lets you control this performance issue.

You can manage index fragmentation with the FILLFACTOR parameter. The values from 1 to 100 represent the percentage of a data page that needs to be full when the index is created. Put another way, a value of 1 allows any amount of fragmentation, whereas 100 will make each page full when the index is created or recreated. In reality the actual fill factor will be no more than the amount specified. FILLFACTOR is also affected by the type of page used, whether it's a leaf-level or non-leaf-level page.

You can specify the FILLFACTOR at the time of indexing as follows:


    ON [tablename] (IDname)


A re-index operation is specified as follows:

    DBCC DBREINDEX 9'[tablename]', '', 60)

Fragmentation on a non-leaf-level page occurs when there's a change in the allocation of pages at leaf level in an index, so leaving a free entry on each page is normal practice. For situations where there are many new rows and pages to be added at the leaf level, you can specify a PAD_INDEX factor which applies the FILLFACTOR value you set to non-leaf pages also. Thus you would do the following when you want to specify both page types:


    ON [tablename] (IDname)


Finally, to limit data page fragmentation, create a clustered index with a FILLFACTOR of 100, and if necessary rebuild the index if it already exists with this value using:

    DBCC DBREINDEX ('tablename', packed_tablename, 100)

You'll also want to drop the unpacked clustered image after you create the packed one.

Dig Deeper on Windows Server storage management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.