THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance Impact: file fragmentation and SAN -- Part I

1KB Sequential Writes


It’s well known that disk I/O performance can be severely impacted by fragmentation at the file system level. In other words, when a file is allocated space from many small fragments, its performance can be much worse than when its space is allocated from a single contiguous chunk. The impact is most pronounced with sequential I/Os.


Disk defrag tools vendors have been trying very hard to get that message across to you through their aggressive web and email ad campaigns, creating an impression that if you don’t run their file defrag tools regularly, your file system performancewould suffer greatly.


On a traditional directly attached storage, you might as well heed their advice and keep your drives defraged regularly.


However, on drives that are presented from some enterprise disk array over a SAN, the impact of file fragmentation may not be as severe, or not severe at all, and running the defrag tools constantly only serves to burn disk I/Os for nothing or not very much.


To see the impact of file fragmentation on a drive presented from a high end enterprise class disk array, 1KB sequential writes wee tested on a 10GB test file in the following two scenarios:


  1. The 10GB file was created on a freshly formatted empty drive, thus without any fragmentation at all.
  2. The freshly formatted drive was first filled to the full capacity with 2MB files, and then some of these 2MB files were randomly deleted to make sufficient room for the 10GB test file. In this case, the 10GB test file was extremely fragmented.

The following chart shows the results of many repeated tests, applying 1KB sequential writes at various load levels:




Clearly, on this drive, severe file fragmentation had no impact on the performance of 1KB sequential writes.


I don’t mean to suggest that file fragmentation does not have any impact on the I/O performance on any SAN/disk arrays. I simply don’t know if there is a significant impact on systems I have had no experience with. For your particular SAN environment, you have to run tests to find out yourself.


The intent of this blog post is to highlight the fact that the impact of file fragmentation may not be as universal as some defrag tools vendor may want to lead you to believe.


Published Sunday, December 7, 2008 5:57 PM by Linchi Shea

Attachment(s): image002.gif



Brent Ozar said:

Agreed, writes shouldn't be an impact. How about reads across large tables though, like table scans in a data warehouse? I'm guessing the impact there would be larger if the reads are scattered all over the spindles.

December 7, 2008 5:36 PM

Linchi Shea said:

I'll report the results from other I/O workloads. But the point here is to contrast the results with those from the traditional directly attached storage where even for 1KB sequential writes, file fragmentation can make a big difference. Note that SQL Server log writes may perform 1KB sequential writes.

December 7, 2008 6:48 PM

Linchi Shea said:

Also, I'd like to note that it's difficult to conclude that something doesn't have any performance impact because you would have to test all the workload combinations before you can even try to draw that type of conclusion. It's not my intention to state that file fragmentation does not have any performance impact on a SAN drive. Just want to make that clear. The intent is to compare DAS and SAN on specific workloads with respect to file fragmentation.

December 7, 2008 7:39 PM

Andrew Kelly said:

It would be very helpful to know what portion of those wrties were totally buffered by the cache.  It is impossible to say with so little info but it appears to me as if all were.  How much cache did the SAN have and was this test the only thing using it?  

December 7, 2008 7:51 PM

Linchi Shea said:


When I say 'high end enterprise class SAN', it implies that all writes are cached, especially when the test file is 10GB. And that's one of the reasons that file fragmentation may not be such a big deal with a SAN drive, but it's not the only reason. The other is that most of these SAN drives are inherently fragmented in terms of track-to-track seek time and average seek time even if cache inside the disk array is not sufficiently large to cache eveyrthing.

Thanks for helping to make this explicit.

December 7, 2008 8:24 PM

Grumpy Old DBA said:

I've always been told by san vendors that fragmentation doesn't matter; but if you consider a db of say 200gb that has several hundred fragments my feeling is that there is a danger of sequential io becoming random io and read ahead and such becoming inefficient. We always talk about high volumes of san cache but 128gb of cache may sound high but what if there are 128 luns ( or more ); the cache is goping to be split read/write probably anyway. I have to admit these types of questions have never been answered to my satisfaction. I'm currently running some tests against a new san and the experience is proving "interesting" to say the least!!

December 8, 2008 5:42 AM

Linchi Shea said:

1KB Sequential Writes on DAS There were some questions about the use 1KB sequential writes in my previous

December 8, 2008 1:57 PM

Linchi Shea said:

I would not expect a defrag tool vendor to say that fragmentation at the logical drive level does NOT have a substantial impact :-). Nor am I suggesting that they are lying as I'm sure they can find empirical evidnece to support their claims. All I wanted to achieve is to throw some empirical evidence I have collected out there and let people have a different perspective and perhaps motivate them to do some tests themselves to find out whether it has or has no impact in their own environments.

One thing is for sure, that is, you can't just take for granted whatever a vendor is giving you, no matter who that vendor is. You have to give that tire a good kick yourself.

December 8, 2008 6:50 PM

Linchi Shea said:

256KB Sequential Reads In my two previous posts ( 1 , 2 ), I highlighted the fact that while file fragmentation

December 10, 2008 12:19 PM

Linchi Shea said:

Lies, damned lies, and statistics! If you have read my three previous posts ( 1 , 2 , 3 ), you may walk

December 22, 2008 11:40 AM

Sebastian Mai said:

What I am missing in all your performance articles is the real cluster size of the formatted partition. On one size you are talking about how you set up the fragmentation on the disk but which clustersize on disk was used to simulate the fragmentation?

Or did you also took this in consideration and modified the clustersize of the partition to the appropiate fragment e.g 1 kb , 4 kb , 128 kb ?

Thx for your feedback.

March 11, 2009 7:10 AM

Confused said:

It's good to see some analysis going into the topic of shared storage system but without knowing what the graph is actually showing I am having problems with the information presented by the graph - it just looks to "good" which makes me wonder what the graph is actually telling us.

Following on from this thought if the chart shows "applying 1KB sequential writes at various load levels" and that "severe file fragmentation had no impact on the performance" assuming the x axis is load we could deduce that increasing load has no impact on performance which sounds to good to be true. If we are performing a performance test that does not place a sufficiently high load on the system that we can see a deterioration in performance towards the upper end of the load then we are not really testing the system that we intended. An example would be one of those gaming video card comparisons where at the lower resolutions everything seems roughly the same and one resolution produces much the same results as the next and as resolutions increase the figures start to change and actually give us some idea of where each card falls down.

Not to say I don't like what you are trying to do - because I do - but there is just that thing missing from the results.

May 1, 2009 10:10 AM

Mark said:

I read the article posted here and I wanted to provide the following comments with regards to your testing.

Whenever testing is performed, it is important to provide as much detail as to your testing as possible. This will ensure that any testing that is performed is done properly and the conclusions that are drawn from this testing are accurate. In this article, the following information was either not included or not provided?

1.It was mentioned that “some of these 2MB files were randomly deleted” during the test. Which 2MB files were deleted?  Depending upon which files were deleted, this would affect the amount of fragmentation.

2.How fragmented was the drive when the test was performed? (Do you have a volume map or other information of the drive showing the fragmentation?)

3. How many fragments was your 10GB file in?

4. Did you perform any tests with the any larger SQL files?

5. How did you perform the 1K sequential writes under various load levels? More information as to how this was tested would be appreciated.

I also noticed that you did not mention anything about read times. Fragmentation affects write times when data is being written to fragmented area of the disk or volume. However, fragmentation also affects read time when accessing and \or reading files that are in a fragmented state. I would suggest that you use a third party application such as readfile available at the following url to test read times as well:

There are many IT managers and CIOs of major corporations using defragmentation software on their SQL servers in their organization. Many of these individuals have run their own tests and came to the conclusion to buy defragmentation software for their computers and servers. Since you mentioned that you “don’t know if there is a significant impact”, I would suggest that you re-run your tests and address the concerns I mentioned above.

October 23, 2009 6:20 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement