Testing Times - mdf fragmentation
Now I've never been convinced that file level fragmentation is irrelevant on a SAN, at least two sources have assured me that this is the case, but then they also assured me of a number of other points most of which it appears I have proved to be basically incorrect. < grin >
Now I've followed with great interest a series of posts by Linchi Shea http://sqlblog.com/blogs/linchi_shea/ concerning SAN fragmentation, if you don't subscribe to this blog then you're really missing a great source of technical knowledge, without this blog I would have struggled ( well I still struggled but that's another story ) with trying to get the storage teams to consider HBA queue depth for a start.
So I decided to come up with the insane fragmentation test: Assume that there is no DBA and you're creating a number of databases on your server; here's what I did;
I created three databases and then ran my test 1, which creates and populates a 1 million row table of approx 8.5GB, in each database simultaneously. The population of the databases caused auto growth and true to form windows + sql server managed to totally fragment all three databases despite ample free space on the LUN.
On a serious note this should be taken as a warning as to what can happen if you don't manage database growth. Here's the output showing my fragmented databases
The three databases are called Stresful, rubbish1 and rubbish2.
-
I then emptied the datafiles of data and then ran my scripts which populated the three databases with 1 million rows of data each, I did this three times.
-
I dropped all the databases, created non fragmented databases and repeated the test three times.
-
The three runs against the fragmented databases took on average 106 mins.
-
The three runs on non fragmented databases took on average 16 mins
It's not for me to name the particular SAN that I used for this test but it is a serious enterprise bit of kit and is claimed to be able to scale with every condition known to man and maybe a few more! To the best of my knowledge the HBAs are 4GB, there's more than 1 of course because this test was run on a cluster.
-
Logically as this was all data inserts I knew the performance would be bad and I intend to repeat the test with a smaller number of fragments and to test updates as well as inserts, see my other posts.
-
We don't always have just one database on our servers and we don't always have a sperate lun for each mdf file, much as some of my tests showed that creating multiple files ( not filegroups ) for your database could degrade performance the movements of the disk heads I would suggest are the factor here.
Linchi only did a test on single files, maybe running multiple tests would produce a different result.
Maybe the particular SAN I'm testing against is different, sadly like probably most DBAs I don't have acess to multiple hardware platforms, it's only due to a data centre migration I've been able to run these tests, I'm attempting to show that the migration does not bring any degredation of performance compared to our current data centre.
I should give a quick few words of thanks to Tony Rogerson http://sqlblogcasts.com/blogs/tonyrogerson/ who has fended a number of my questions and who also run SAN benchmarks http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/09/22/1089.aspx
I hope this post formats correctly - always a bit of a gamble when adding images!