Fragmentation and the impact on Sql Server

Whenever I’m called into a client site to review performance issues, one of the first things I always check is the filesystem fragmentation levels. Fragmentation is something that degrades performance on all databases to verying degrees. Heavily fragmented disks degrade performance by 10-15% for sequential operations (Backup, DBCC, full table scan etc) and 100’s  of percent for operations where multiple tables are accessed in parallel (queries with lots of joins). Drew Robb wrote an interesting article which illustrates the fragmentation issue in more detail http://www.dmreview.com/dmdirect/20020823/5658-1.html  
In light of the performance impact of heavily fragmented filesystems it can be a “no brainer” to implement defragmentation routines, IF there is a window of opportunity to do it.Typically I use 2 methods of defragmenting (windows defrag and sysinternals contig) just because they are free and easy to schedule as batch files. Contig will force files to be contiguous if the space is available to do this. Windows defrag will also make files contiguous but will also attempt to compact the files so that they are close to the inside of the disk, however I find Windows defrag  less effective than contig at making files contiguous but gives a different benefit of compacting the files. Here is a sample batch script that I execute on a weekend (the deep scrub): 
[ContigPath]\contig -s c:\ > contig.log

[ContigPath]\contig -s d:\ >> contig.log
  
Here is a more typical weekday script: 
defrag -f c:\ >  defrag.log
defrag -f d:\ >> defrag.log
   
Sysinternals contig can be downloaded from here:http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

 

Published 23 May 2008 15:05 by blakmk
Filed under: ,

Comments

No Comments