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?
Best Answer
PAD_INDEX
only applies to non leave level pages. By default those are filled full.So specifying
PAD_INDEX=ON
together withFILLFACTOR
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.