Without a clustered index the base table isn't going to change following
dbreindex or defrag. They only deal with indexes. The showcontig below
is for indexID = 0 which is a table with no clustered indexes.

Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************

-----Original Message-----
From: Lindeman Luanne
[mailto:mssqldba-ezmlmshield-x98637086.[Email address protected]
Sent: Tuesday, February 27, 2007 10:38 AM
To: LazyDBA Discussion
Subject: Why doesn't dbreindex or indexdefrag change results in
showcontig?


Hi,

When I runn DBCC SHOWCONTIG on a table with no indexes, I get

DBCC SHOWCONTIG scanning 'AssocLocation' table...
Table: 'AssocLocation' (226815870); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.7
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 3128.6
- Avg. Page Density (full).....................: 61.35%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Should I be worried that the scan density is only 33%? And how do I
correct that if there is no index?

Also, when I run DBCC SHOWCONTIG on a table that has indexes - sometimes
after running DBCC DBREINDEX or DBCC INDEXDEFRAG the scan density
doesn't change. Does anyone understand why in some cases those two
statements don't seem to change anything? It is my understanding that
scan density should be near 100% and extent scan fragmentation should be
close to 0 - is that thinking correct?

Thanks everyone!

Luanne


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
Confidentiality Note: The information contained in this
message, and any attachments, may contain confidential
and/or privileged material. It is intended solely for the
person(s) or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in
reliance upon this information by persons or entities other
than the intended recipient(s) is prohibited. If you received
this in error, please contact the sender and delete the
material from any computer.
****************************************************************

MS Sql Server LazyDBA home page