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:

    CREATE NONCLUSTERED INDEX IDname

    ON [tablename] (IDname)

    WITH DROP_EXISTING, FILLFACTOR = 85

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:

    CREATE NONCLUSTERED INDEX IDname

    ON [tablename] (IDname)

    WITH DROP_EXISTING, FILLFACTOR = 85, PAD_INDEX

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.

This was first published in January 2005

Dig deeper on Windows Storage Systems Hardware

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchServerVirtualization

SearchCloudComputing

SearchExchange

SearchSQLServer

SearchWinIT

SearchEnterpriseDesktop

SearchVirtualDesktop

Close