Testing Times 2
Just sometimes it's possible to get to a position of not being able to see the wood for the trees.
I'm indebted to Paul Randal http://sqlskills.com/blogs/paul who returned me to normality after one of those lapses of reasoning.
You'll see in my previous post that I'm currently establishing some performance baselines ahead of a move to a server upgrade.
As part of my upgrade strategy to SQL2005 I was considering making the database sit on multifile database ( not filegroups ) so I figured that this could be an opportune time to test this out with my sql tests.
Sadly the first tests on a multifile database showed quite a severe performance degredation and I was at a loss to quite understand why this should be so on raid sets.
Paul pointed out the obvious, each file will cause head movement on the disks as the round robin of the multiple files every 64kb will have the data hopping all over the place. So after a quick "Doh" I thought about my setup, the 4 disk raid 10 would obviously not work so well as there are only two disks, but my san with lots of spindles should work fine - or not.
I have to complete the full set of tests but indications are that for my tests at least a multifile database is a very bad idea.
I have a 16 core server so had used 8 files in the primary filegroup; performance appeared to degrade roughly 50%, which is significant.
Full details by the end of the week!