Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to work out what setting for PAD_INDEX I should use for a clustered index on a IDENTITY column, the table is rarely going to be updated after first insert so I am looking for the setting that will result in the most of the space in the pages being used.

However I have found some documentation on the MSDN that seems to conflict with each other (or at least confuse me enough to ask for clarification).

From: http://msdn.microsoft.com/en-us/library/ms174979.aspx

PAD_INDEX = { ON | OFF } When ON, the percentage of free space specified by FILLFACTOR is applied to the intermediate level pages of the index. When OFF or a FILLFACTOR value is not specified, the intermediate level pages are filled to near capacity leaving enough space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages. The default is OFF.

And from: http://msdn.microsoft.com/en-us/library/ms177459.aspx

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Given the clause When a FILLFACTOR value is not specified, does that mean that the default FILLFACTOR of 0 is ignored regardless of the setting of PAD_INDEX?

Following on from that does that mean that I need to specify both PAD_INDEX = ON and FILLFACTOR = 100 on my identity clustered index to get maximum page usage?

share|improve this question
    
The first one applies to intermediate level index pages only, while the second one is about leaf pages. I can't test it, but from these I'd say that if you set FILLFACTOR to 100, your pages (at all levels) will be filled to capacity, regardless of the setting of PAD_INDEX. –  dezso Aug 31 '12 at 8:16
add comment

migrated from serverfault.com Aug 31 '12 at 3:48

This question came from our site for professional system and network administrators.

1 Answer

PAD_INDEX only applies to non leave level pages. By default those are filled full.

So specifying PAD_INDEX=ON together with FILLFACTOR is only useful when you specify a fillfactor smaller then 100%.

In your case, specifying PAD_INDEX is not necessary, since by default your leave pages are how you want them. Full.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.