File fragmentation? What file fragmentation?

A few years back a SAN admin told me not to worry about physical file fragmentation because “The SAN takes care of it” and I took them at their word and, probably along with a lot of fellow DBAs who have their back-ends (in a manner of speaking) on  a SAN, allowed myself to wallow for too long in this cosey myth.

It ain’t so

Recently, I’ve found that not all SANs “take care of it” as well as you might think.

In fact, the documentation for this particular SAN (which shall remain nameless in order to protect the guilty) suggested that an OS level defragmentation should be carried out regularly (?!).

I’ve hunted around the world-wide-inter-twitter-blogosphere and there appears to be no clear consensus and even some contradictory posts on the matter.

I imagine this is because some SAN vendors handle fragmentation better than others and that, to an extent, files are by definition fragmented as they are striped across multiple disks in a SAN. Even so, fragmentation within these stripes must affect performance on traditional spinning disks.

I’m just going to recount my recent experiences and let everyone make up their own mind by showing how easy it is to ‘see’ file fragmentation occurring on a Windows Server 2008R2 system running SQL Server 2008R2 (although file fragmentation itself is a universal issue and not really governed by OS or SQL Server version), and what I noticed when I had dealt with the file fragmentation that I uncovered on an established system that had only been running for about a year-and-a-half at the time (but long before it came under my wing, I hasten to add!).

Tool up

First of all, we’ll need a tool which actually shows file fragmentation. If you use Windows Explorer to tell you how fragmented your database files are, expect it to tell you everything’s good, there’s no file fragmentation here, move along now.
That’s because (by default) Windows does not consider any file over 64 MB to be fragmented, and if you’re using the GUI interface accessed via Windows Explorer to check for it you’ll be none the wiser – only running defrag via the command line at the command prompt actually states

“Note: File fragments larger than 64MB are not included in the fragmentation statistics.” as part of the output of a file fragmentation analysis invoked via the /A parameter.

I have to admit, this did fool me for a while as I was investigating one particular database file that was showing significant IO latencies compared to other files in the same database located on the same partition, even after going through all the usual steps of looking at partition alignment, sector sizes, index defragging, high contention tables on that file etc etc.

Resolving file fragmentation

As soon as I uncovered the article outlining the changes to Windows disk defragmentation and a bit more digging around, I went to that mainstay of all Windows troubleshooting sites, SysInternals, and downloaded contig.exe. This great little tool will show file fragmentation of individual files or directories and allow them to be defragmented.

I pointed it at the 180GB problem file and I used the -v parameter to list all the fragments, expecting maybe a couple of dozen fragments. The output can be seen in fig.1 below:

it actually filled the screen and scrolled and scrolled and eventually reported a shocking 4,818 file fragments (the picture has been altered to show the relevant output and obfuscate the name of the file concerned).

The output lists the starting offset and and the number of allocation units it contains (multiply this by the allocation unit size that the partition concerned was formatted with to get the size of that fragment). Some of the fragments were only 64K in size. I’ve no idea how there could be such tiny fragments (even allowing for default autogrow settings), other than the possibility that at some point there was little free space on the partition concerned and the remaining free space was highly fragmented, so space was allocated wherever it could be found until the partition was resized (although this is purely speculation on my part).

Incidentally, contig.exe can be used to analyse free space fragmentation and report your biggest free fragment via the -f parameter.

Not only that, but it can also defragment files, can defragment one file at a time if required, and it can do that all that whilst the database is online, although I would personally never risk trying that on a live production system.

I had to deal with this particular issue in a slightly different way:

  • first, I added additional files to the filegroup
  • then I shrunk the original highly fragmented file which would redistribute the data onto the unfragmented files added to the filegroup and capped the original file to a low maximum size (4GB). I’ll explain why later
  • a reindex was then carried out, as shrinking creates internal index fragmentation

Ideally, I would have just used contig.exe to clear the fragmentation, but there were other factors involved here which forced my hand. Firstly, the file was on a partition that had been created with an incorrect allocation unit size, and this proved a convenient way of both resolving the fragmentation and moving the bulk of the filegroup to a partition created with a 64K allocation unit size, whilst keeping the database online. As the file concerned was the Primary mdf, I could not empty and and drop the file, which is what I would ordinarily have done under these circumstances, and a detach/attach operation to move it to the correctly formatted partition would have involved downtime. Secondly, (and this may affect others as well) contig.exe does not defragment files on mount points and, unfortunately, this database was utilising mount points.

There are ways around this, like using the subst command, but the system in question was clustered as well, so I wasn’t prepared to mess around with it too much.

What’s the big deal?

Backup performance went up 10% immediately after resolving the file fragmentation  and read latency went from 89ms to 13ms, or an improvement of @ 85%. Reindexing performance also improved because certain indexes were not internally fragmenting as frequently as they were before the physical defragmentation.
This kind of of improvement is usually achieved by throwing a lot of money and tin at a problem, but it cost us nothing, so I think it’s a big deal.

See for yourself

It’s not difficult to simulate how a database can become physically fragmented:

  1. Create a small, single data file database with a data file of at least 64MB (on a test system or even your desktop) on a partition that has been in use for a while (i.e. not a pristine empty partition).
  2. Run contig against the mdf file and you’ll see it as one contiguous file (unless the free space on the system concerned has become massively fragmented).
  3. Mimic several autogrows by manually altering the size of the data file and growing it by e.g. 1MB at a time.
  4. Re-run the contig.exe check against that data file. Note the increase in fragments (there probably won’t be a 1:1 ratio as some of the growths will be appended  to existing fragments).
  5. (Optionally) run the Windows fragmentation report which will report no fragmentation as, by default, it’s ignoring files bigger than 64MB.

OK, so this is a somewhat contrived simulation, but this will be what is happening to any databases that have not been appropriately pre-sized and have been autogrowing over time, and after countless autogrows (and probably a few shrinks thrown in for good measure) will be potentially suffering from performance impacting file fragmentation on systems using traditional spinning disks.

On media where the seek time is equal to all parts of the disk, like SSDs, the hit is probably not noticeable, but on spinning media, with enough file fragmentation the impact can be noticed but usually only with hindsight, after the fragmentation has been resolved.

What’s more, and here’s the kicker, if you’re not using third party file fragmentation reporting tools, you don’t know it’s there as the default Windows file fragmentation report will not return stats for files larger than 64MB.

Will it work for me?

The first thing I would do is check out the IO performance of the database(s) in question.

From personal experience, some of the things to look out for include files performing worse than others on the same partition or SAN back-end even though they are not responsible for most of the IO against that database and particular indexes regularly showing high internal fragmentation.

Check the output of contig.exe when aimed at those files. If you see hundreds of file fragments for an individual file on a partition utilising spinning disk technology, it’s definitely worth exploring.
Use the resolution technique most suitable to the SLAs and the situation; if you have the luxury of downtime, the files can probably be defragmented more quickly as there will be less IO contention. If contig.exe cannot be used to defragment the file you can try the approach I was forced to use.

Whatever you do, get a baseline of the latencies before you start so you can compare this to the corresponding figures after the file fragmentation has been removed. Don’t forget, things like reindex duration, DBCC CHECKDB duration and backup durations before and after resolving the file fragmentation can also provide a tangible indication of improvements (or the lack of).