Issues with DBCC SHOWCONTIG

I am trying to resolve database fragmentation and I am having two issues wit
h
the DBCC SHOWCONTIG command.
1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
different values for logical fragmentation then running DBCC
SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
should be considered as correct?
2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
TABLERESULTS shows results for indexes that do not appear in the QA Object
Browser or the EM Manage Indexes/Relationship window. Consequently, running
DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
index name. What is going on here?
BONUS QUESTION:
1. Which is a more accurate reflection of fragmentation: Logical
Fragmentation or Scan Density?1) Can you elaborate, or show an example?
2) What indexid? Remember that 0 is the datapages for a table, and that is n
ot an index.
3) Logical Fragmentation. Scan Density will be (as I remember) removed from
next version as it will
show bogus values of the filegroup have more than one file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
>I am trying to resolve database fragmentation and I am having two issues wi
th
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently, runnin
g
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?|||1. This is known bug in SQL Server 2000. If you specify ALL_INDEXES or all
tables (by not passing in any parameter), logical fragmentation information
for some indexes might be a little bit off (It does not matter if the index
is large; the difference will show up if the index is small).
The problem is fixed in SQL Server 2000 SP4 (which is in Beta stage now).
2. I have no clue on this. Remember, index could be dropped between you
call DBCC SHOWCONTIG and when you use DBCC INDEXDEFRAG. That might be the
problem you see.
Bonus 1: Agree with Tibor's reply, Logical Fragmentation, with one
correction: for backward compatiable, Scan Density will still be supported
in DBCC SHOWCONTIG when SQL Server 2005 ships. (DBCC SHOWCONTIG will be
deprecated and replaced by a dynamic managed view - checkout SQL Server 2005
Beta 2 for more details)
Hope this helps.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:D90B5D30-BAA3-4114-8A51-48ECF2DD714D@.microsoft.com...
> I am trying to resolve database fragmentation and I am having two issues
with
> the DBCC SHOWCONTIG command.
> 1. Running DBCC SHOWCONTIG WITH FAST, ALL_INDEXES, TABLERESULTS shows
> different values for logical fragmentation then running DBCC
> SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES, TABLERESULTS. Which value
> should be considered as correct?
> 2. For some tables DBCC SHOWCONTIG(<tablename> ) WITH FAST, ALL_INDEXES,
> TABLERESULTS shows results for indexes that do not appear in the QA Object
> Browser or the EM Manage Indexes/Relationship window. Consequently,
running
> DBCC INDEXDEFRAG on those indexes results in an error on Parameter 3, the
> index name. What is going on here?
> BONUS QUESTION:
> 1. Which is a more accurate reflection of fragmentation: Logical
> Fragmentation or Scan Density?