SQL Server Performance

Disk Defraging Pointless or Beneficial??

Discussion in 'Performance Tuning for Hardware Configurations' started by Ziptar, Mar 21, 2007.

  1. Ziptar New Member

    I checked the fragmentation level of the SAN Volumes on one of our SQL 2000 servers yesterday.

    Drive look like this

    Boot O/S Drive:

    Volume fragmentation
    Total fragmentation = 25 %
    File fragmentation = 51 %
    Free space fragmentation = 0 %

    Trans Log Volume:

    Volume fragmentation
    Total fragmentation = 35 %
    File fragmentation = 71 %
    Free space fragmentation = 0 %

    SAN Volume for Main File group:

    Volume fragmentation
    Total fragmentation = 49 %
    File fragmentation = 99 %
    Free space fragmentation = 0 %

    There are 3 other volumes for other file groups that are in great shape.

    I have looked at Disk Defragging for SQL Server several times over years and I can never seem to find a definitive argument for doing it or not doing it.

    I am starting to think that maybe once a week after DBCC INDEXDEFRAG is done couldn't be a bad thing?

    I am looking at and have been playing with a trial of Diskeeper 2007 Server on a Dev / Test box works well so far without any apparent negitive impact on DB performance but, not as huge as our production DB's either.

    So, Defrag or No? That is the question.
  2. joechang New Member

    there might be some benefit with direct attach storage
    but on a san, even if you defragment the file seen by the OS,
    you still do not know the true placement of the file

    in general, with large database files,
    start with a fresh format of the partition
    create the file with adequate space, and fragmentation is never an issue

    i would run perfmon to make a note of the peak random and sequential IO rates
  3. mmaterie New Member

    Defragmentation of the file system is still of benefit. It is not vital to have DAS or for the fs to directly map to a physical location. The benefit here is a reduction in unnecessary file system I/O. As noted, Perfmon is a good way to measure the realized benefit.

    I also agree with the pre-allo of the DB, and the use of approriate clusters sizes. That will help, but won't absolve fragmentation, as expansion will likely be needed at some time in the future (unless you have a liberal budget for allocating storage right now).

    Michael Materie
    Diskeeper Product Manager

Share This Page