Another good argument to place clustered indexes on all tables.

Quoting Davis Ralph <mssqldba-ezmlmshield-x76136378.[Email address protected]

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




MS Sql Server LazyDBA home page