SQL Database physical file fragmentation

Solution 1:

I think this article gives an excellent overview of defragmentation of SAN drives

http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php

Basic points is that defragmenting is not recommended on SAN storage because it is difficult to correlate the physical location of blocks on the disk when the location has been virtualized by the SAN when presenting the LUN.

If you were using RAW device mappings or you have direct access to a RAID set that is the LUN you are working with, I could see degfragmentation having a positive effect, but if you're given a "virtual" LUN off a shared RAID-5 set, no.

Solution 2:

Multiple parts to this question and answer:

Physical file fragmentation isn't really relevant for Enterprise SAN storage, as Kevin already pointed out - so nothing to add there. It does really come down to the I/O subsystem and how likely you are to be able to make the drives go from more-random I/Os when performing a scan to more sequential I/Os when performing a scan. for DAS, it's more likely you will, for a complex slice-n-dice SAN, probably not.

File-system level defragging - only do it with SQL shut down. I've never experienced problems myself here (as I've never performed an online, open-file defrag of SQL database files) but I've heard plenty of anecdotal evidence from customer and clients of weird corruption problems occuring. General wisdom is not to do it with SQL online.

Index fragmentation is completely orthogonal to file fragmentation. SQL Server has no idea of file-fragmentation - too many virtualizatin layers in between for it to have any hope of working out actual I/O subsystem geometries. Index fragmentation, however, SQL does know everything about. Without repeating myself too much from the answer you already referenced, index fragmentation will prevent SQL doing efficient range-scan readahead, regardless of how fragmented (or not) the files are at the file-system level. So - absolutely you should mitigate index fragmentation if you're seeing degrading query performance.

You do not have to do these in any particular order, although if you take care of file-system fragmentation and then rebuild all your indexes and cause more file-system fragmentation by growing multiple files on a defragged volume, you're probably going to be ticked off. Will it cause any perf issues though? As discussed above, it depends :-D

Hope this helps!

Solution 3:

What's the best way to fix physical file fragmentation on a production SQL box?

I run SYSINTERNALS' contig on my database files.

See http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Solution 4:

I would recommend sizing the db appropriately, shutting sql server down, copy the database file to another disk array, and then copy it back to defrag it. Much faster than using windows defrag in my experience.