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!