If index is in use at the time of DBCC INDEXDEFRAG
The indexfrag skip that index and do not make any changes.
But whatever I know DBCC DBREINDEX rebuilt it hardely matter it ios in use
or not.......................
Thanks & Regards,
Uma
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
>
---------------------------------------------------------------------
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
Confidential: This electronic message and all contents
contain information from TRANS CONTINENTAL e-SERVICES, PVT LTD . which may be
privileged, confidential or otherwise protected from disclosure. The
information is intended to be for the addressee only. If you are not the
addressee, any disclosure, copy, distribution or use of the contents of this
message is prohibited. If you have received this electronic message in error,
please notify the sender immediately and destroy the original message and all
copies.
MS Sql Server LazyDBA home page