DBCC DBREINDEX

There is a parameter in DBCC DBREINDEX that allows you to specify the
freespace on leaf level pages (fillfactor). This is only use when the index
is created and when the DBREINDEX command is run.
In SQL2005, there is a new parameter in the create index which is
'PAD_INDEX' this determines the amount of free space on the intermediate
pages of the index. When you run DBREINDEX the fill factor applies to the
leaf levels but does it also apply to the intermediate pages, or is there
another parameter I may be missing here?
Thanks in Advance
Dave
Dave
You are probably talking about ALTER INDEX operation
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. If FILLFACTOR is not specified at
the same time PAD_INDEX is set to ON, the fill factor value stored in
sys.indexes is used.
OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity. This leaves
sufficient space for at least one row of the maximum size that the index can
have, based on the set of keys on the intermediate pages.
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave
|||Hi Uri,
I understand that but my question is does this 'PAD_INDEX' facility also
work with DBREINDEX?
Dave
"Uri Dimant" wrote:

> Dave
> You are probably talking about ALTER INDEX operation
> 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. If FILLFACTOR is not specified at
> the same time PAD_INDEX is set to ON, the fill factor value stored in
> sys.indexes is used.
> OFF or fillfactor is not specified
> The intermediate-level pages are filled to near capacity. This leaves
> sufficient space for at least one row of the maximum size that the index can
> have, based on the set of keys on the intermediate pages.
>
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>
|||Dave
I have no idea, but what I do know that DBCC DBREINDEX is deprecated
and will be removed in the future
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:5E727125-DDB0-44CD-8287-886D9D1436E2@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
>
> "Uri Dimant" wrote:
|||PAD_INDEX has always been there it is not a 2005 feature. But it does not
take a value for the space. Instead it is either on or off. If it is on it
will inherit the fill factor setting and use it for the intermediate levels
of the index just like the fill factor is for the leaf level. It is not
maintained except for rebuilds and creation.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave
|||Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
change this if required?
Thanks in Advance
Dave
Dave
"Andrew J. Kelly" wrote:

> PAD_INDEX has always been there it is not a 2005 feature. But it does not
> take a value for the space. Instead it is either on or off. If it is on it
> will inherit the fill factor setting and use it for the intermediate levels
> of the index just like the fill factor is for the leaf level. It is not
> maintained except for rebuilds and creation.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>
|||It is OFF by default and if you want to change it you need to specify it in
the create or rebuild index commands.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...[vbcol=seagreen]
> Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
> What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
> change this if required?
> Thanks in Advance
> Dave
>
> --
> Dave
>
> "Andrew J. Kelly" wrote:
|||Thanks Andrew
Dave
"Andrew J. Kelly" wrote:

> It is OFF by default and if you want to change it you need to specify it in
> the create or rebuild index commands.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...
>
>