Disk Defraging Pointless or Beneficial?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Disk Defraging Pointless or Beneficial??

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.
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
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