You are here: Re: index fragmentation LogicalFragmentation -how bad is bad - silly question alter « MsSQL Server « IT news, forums, messages
Re: index fragmentation LogicalFragmentation -how bad is bad - silly question alter

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация