Performance Impact: file fragmentation and SAN -- Part I

re: Performance Impact: file fragmentation and SAN -- Part I

Brent Ozar — Sun, 07 Dec 2008 22:36:08 GMT

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.

re: Performance Impact: file fragmentation and SAN -- Part I

Linchi Shea — Sun, 07 Dec 2008 23:48:50 GMT

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.

re: Performance Impact: file fragmentation and SAN -- Part I

Linchi Shea — Mon, 08 Dec 2008 00:39:16 GMT

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.

re: Performance Impact: file fragmentation and SAN -- Part I

Andrew Kelly — Mon, 08 Dec 2008 00:51:12 GMT

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?

re: Performance Impact: file fragmentation and SAN -- Part I

Linchi Shea — Mon, 08 Dec 2008 01:24:33 GMT

Andy;

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.

re: Performance Impact: file fragmentation and SAN -- Part I

Grumpy Old DBA — Mon, 08 Dec 2008 10:42:53 GMT

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

Performance impact: file fragmentation and SAN – Part II

Linchi Shea — Mon, 08 Dec 2008 18:57:35 GMT

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

re: Performance Impact: file fragmentation and SAN -- Part I

Linchi Shea — Mon, 08 Dec 2008 23:50:08 GMT

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.

Performance Impact: file fragmentation and SAN – Part III

Linchi Shea — Wed, 10 Dec 2008 17:19:13 GMT

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

Performance impact: file fragmentation and SAN – Part IV

Linchi Shea — Mon, 22 Dec 2008 16:40:24 GMT

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

re: Performance Impact: file fragmentation and SAN -- Part I

Sebastian Mai — Wed, 11 Mar 2009 11:10:41 GMT

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.

re: Performance Impact: file fragmentation and SAN -- Part I

Confused — Fri, 01 May 2009 14:10:08 GMT

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.

re: Performance Impact: file fragmentation and SAN -- Part I

Mark — Fri, 23 Oct 2009 22:20:01 GMT

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:

http://www.winimage.com/readfile.htm

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.