Posted by John Bell on 03/10/07 20:49

Hi

"rcamarda" <robert.a.camarda@gmail.com> wrote in message
news:1173530238.674192.247030@64g2000cwx.googlegroups.com...
>I thought I would delve into index fragmentation and I found some
> great sql from many posters (thanks Erland!).
> My question is how bad is bad? I know this is very subjective.
> Some scripts I found would reindex if the LogicalFragmenation is over
> 30%.
In the "Reorganizing and Rebuilding Indexes" topic, BOL recommends to
REORGANIZE for 5-30% and REBUILD > 30%

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm

Although the script on the "sys.dm_db_index_physical_stats" topic in BOL use
10-30% to REORGANIZE

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm

> I have some tables that are 98% (I'm guessing really bad). I know it
> all depends..
>
> more as a learning point: I found a table that had over 30%
> logicalfragmentation, I dropped the indexes, created then ran the
> script that used type code segment:
> 'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,
> NO_INFOMSGS')

DBCC SHOWCONTIG does not support some new features in SQL Server 2005 see
the "DBCC SHOWCONTIG" topic in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1df2123a-1197-4fff-91a3-25e3d8848aaa.htm

>
> In one case, the indexes for the table dropped below 30%, in another
> case the index was still fragmented ever after I dropped and re-
> created index.

Posting the output from sys.dm_db_index_physical_stats would be useful.
>
> SQL Server 2005 x64 SP2
Check you are on the version of SP2 downloaded after 2007-03-05 or the
critical update http://support.microsoft.com/kb/933508 if you have
Maintenance Plans or SSIS packages.

>
If you are using DBCC DBREINDEX or DBCC INDEXDEFRAG you should look to
change to ALTER INDEX...

John

[Back to original message]