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...

quote:

---

> 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.

quote:

---

>--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

quote:

---

>news:556301c3e508$94bae040$a501280a@.phx.gbl...
Density[QUOTE]
>
>.
>

---

|||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...[QUOTE]
> Yes. Thats the index Im trying to defragment.
>
> message
> Density|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?

quote:

---

>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there

---

are no new

quote:

---

>allocations. Therefore the physical page/extent

---

fragmentation will remain,

quote:

---

>even though the rows are now in physical order on the

---

pages. One of the

quote:

---

>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

quote:

---

>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
rows)[QUOTE]
all.[QUOTE]
>
>.
>

---

|||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...[QUOTE]
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
>
> are no new
> fragmentation will remain,
> pages. One of the
> message
> rows)
> all.|||Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 5
NNTP-Posting-Date: 27 Jan 2004 21:22:04 CET
NNTP-Posting-Host: 194.109.252.208
X-Trace: 1075234924 news.xs4all.nl 316 [::ffff:194.109.252.208]:2369
X-Complaints-To: abuse@.xs4all.nl
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!npeer.de.k
pn-eurorings.net!news.cambrium.nl!news.cambrium.nl!news.cambrium.nl!newsgate
.cistron.nl!transit.news.xs4all.nl!newsfeed.xs4all.nl!xs4all!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:327667
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...

quote:

---

> 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=...ls
erver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...

quote:

---

> 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...
>

---

|||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/tr...ze/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...

quote:

---

> 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=...ublic.sqlserver

quote:

---

>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
This[QUOTE]
rows.[QUOTE]
>

---