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
--
DaveDave
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 c
an
> 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:
>|||When the documentation does not speak, it is time to experiment...
Based on the repro script (see below) I conclude that (only) if
PAD_INDEX was used upon index creation, then the index intermediate
levels are padded when reindexing.
HTH,
Gert-Jan
create table Test (id char(900) not null)
go
insert into Test values (1)
insert into Test values (2)
insert into Test values (3)
insert into Test select cast(id as int)+3 from Test
insert into Test select cast(id as int)+6 from Test
insert into Test select cast(id as int)+12 from Test
insert into Test select cast(id as int)+24 from Test
insert into Test select cast(id as int)+48 from Test
insert into Test select cast(id as int)+96 from Test
insert into Test select cast(id as int)+192 from Test
insert into Test select cast(id as int)+384 from Test
insert into Test select cast(id as int)+768 from Test
insert into Test select cast(id as int)+1536 from Test
insert into Test select cast(id as int)+3072 from Test
insert into Test select top 3856 cast(id as int)+6144 from Test
go
create unique clustered index IX_Test on Test(id)
go
sp_spaceused test
-- 11528 KB
go
DBCC DBREINDEX(Test,'',50)
go
sp_spaceused test
-- 18312 KB
go
DBCC DBREINDEX(Test,'',10)
go
sp_spaceused test
-- 91464 KB
go
drop index Test.IX_Test
go
create unique clustered index IX_Test on Test(id)
with (PAD_INDEX=ON, FILLFACTOR=50)
go
sp_spaceused test
-- 20040 KB
go
DBCC DBREINDEX(Test,'',100)
go
sp_spaceused test
-- 11528 KB
go
DBCC DBREINDEX(Test,'',50)
go
sp_spaceused test
-- 20040 KB
go
DBCC DBREINDEX(Test,'',10)
go
sp_spaceused test
-- 160072 KB
go
drop table Test
SkyDave wrote:[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 level
s
> 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 i
n
> 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...
>
>