Re: Weekly slowdown

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Removing fragmentation is a good thing to do in general anyway. If you used
Indexdefrag the statistics will not be updated so you want to run
sp_updatestats at some point. If you run DBCC DBREINDEX it will update the
stats automatically after the rebuilding of the index but is an offline
operation. If you have multiple files in the filegroup Extent fragmentation
is not accurate.

--
Andrew J. Kelly SQL MVP


"DaveK" <DaveK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0D347A05-7A0F-4A4F-AE02-5502C6BE2B12@xxxxxxxxxxxxxxxx
>I looked at the "extent scan fragmentation" on the largest tables using
> showcontig. Mostly they are running in the 90% or above range, although
> when
> there is also
> "logical scan fragmentation" reported, that is often in the 6% range.
> Should these
> indexes be dropped and rebulit or reindexed with dbreindex? DBCC
> indexdefrag did not seem to do much for performance.
>
> DaveK


.

Relevant Pages

  • Re: SQL 2000 Maint. Plans
    ... That does not mean you can not achieve what you need with INDEXDEFRAG. ... pay attention to the Logical fragmentation and not the others. ... > DBCC INDEXDEFRAG reduces external ... > DBCC INDEXDEFRAG are not as complete as the other techniques. ...
    (microsoft.public.sqlserver.server)
  • Re: INDEXDEFRAG on clustered index & log space
    ... So when INDEXDEFRAG runs again, it thinks that most of the pages ... > transaction log as expected. ... > Fragmentation of 0.02%, still writes lots to the transaction log - not ... > I thought running INDEXDEFRAG on lightly fragmented indexes wouldn't ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2000 dbcc indexdfrag
    ... the first time you run this, fragmentation may be really high, and the ... If your indexes are really large, and fragmentation is really high (Over 40% ... then use INDEXDEFRAG to keep ... Greg Jackson ...
    (microsoft.public.sqlserver.server)
  • Re: High Extent Scan Fragmentation
    ... much continuously present extent fragmentation really smacks of multiple ... DBCC DBREINDEX will only fix the problem if there is contiguous free space ... in the file equal to the size of the index being rebuilt. ... > This table and it's indexes are not built on multiple files. ...
    (microsoft.public.sqlserver.server)
  • Re: Delete from takes a very, very long time
    ... > If Extent Scan Fragmentation is 26% I guess that means scan density is ... > use DBCC DBReindex or DBCC IndexDefrag to accomplish ... > GAJ ...
    (microsoft.public.sqlserver.programming)