dbcc indexdefrag not doing anything

sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>|||Yes. Thats the index Im trying to defragment.
>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> sql2k sp3
>> I keep running dbcc indexdefrag on a small(236,492 rows)
>> table and it doesnt change the output of the Scan
Density
>> (best count:actual count) from dbcc showcontig at all.
>>
>> Any ideas?
>> TIA, Chris
>
>.
>|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> Yes. Thats the index Im trying to defragment.
> >--Original Message--
> >Does your table have a clustered index?
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> I keep running dbcc indexdefrag on a small(236,492 rows)
> >> table and it doesnt change the output of the Scan
> Density
> >> (best count:actual count) from dbcc showcontig at all.
> >>
> >>
> >> Any ideas?
> >>
> >> TIA, Chris
> >>
> >
> >
> >.
> >|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there
are no new
>allocations. Therefore the physical page/extent
fragmentation will remain,
>even though the rows are now in physical order on the
pages. One of the
>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
>> Yes. Thats the index Im trying to defragment.
>> >--Original Message--
>> >Does your table have a clustered index?
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >"chris" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> I keep running dbcc indexdefrag on a small(236,492
rows)
>> >> table and it doesnt change the output of the Scan
>> Density
>> >> (best count:actual count) from dbcc showcontig at
all.
>> >>
>> >>
>> >> Any ideas?
>> >>
>> >> TIA, Chris
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
> >--Original Message--
> >Ok, DBCC IndexDefrag does an in-place defrag thus there
> are no new
> >allocations. Therefore the physical page/extent
> fragmentation will remain,
> >even though the rows are now in physical order on the
> pages. One of the
> >hidden limitations of INDEXDEFRAG vs. DBREINDEX.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> >> Yes. Thats the index Im trying to defragment.
> >>
> >> >--Original Message--
> >> >Does your table have a clustered index?
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> >> sql2k sp3
> >> >>
> >> >> I keep running dbcc indexdefrag on a small(236,492
> rows)
> >> >> table and it doesnt change the output of the Scan
> >> Density
> >> >> (best count:actual count) from dbcc showcontig at
> all.
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> TIA, Chris
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
--
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan|||And in addition to that <g>, scan density is useless in the first place if > 1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
> > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> > So if your database has much locking activity during defragmentation,
> > the DBCC command will be less effective.
> >
> > Gert-Jan
>|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > And in addition to that Indexdefrag only works on 1 file at a time.
This
> > means extent fragmentation is useless on multiple files.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:4016C868.AD86E998@.toomuchspamalready.nl...
> > > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked
rows.
> > > So if your database has much locking activity during defragmentation,
> > > the DBCC command will be less effective.
> > >
> > > Gert-Jan
> >
> >
>