Tip

Managing index fragmentation in SQL Server

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

Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.