From MSDN:
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON: The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.
OFF or fillfactor is not specified: The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.
The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.
Turning on PAD_INDEX can have dramatic effects on read performance and memory pressure in large tables. The larger the table the bigger the effect. As a rule I’d say you want to leave it off unless you fall into some NOT UNCOMMON categories.
Basically, you set PAD_INDEX = ON if you expect a lot of random changes to the index regularly.
That helps avoiding index page splits.
I set it on when I expect 30%+ of random records included in the index to be deleted on a regular basis.
Examples:
CONSTRAINT [PK_tbTable] PRIMARY KEY CLUSTERED
( TableId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON fgTable
Sources:
Comments