October 2008 - Posts

13 October 2008 15:28

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!

Posted by GrumpyOldDBA with no comments
Filed under:
07 October 2008 15:41

Testing Times 1

It's an interesting time with being asked to performance test/benchmark a new server setup for a migration of sql server and data centre.
Both current and new servers are SAN attached and it has been what is effectively the testing of the storage which has proved interesting.
 
To add to the mix I have a similar spec server with internal disk and I ran a couple of tests on one of my my home servers ( essentially a pc with lots of disks )
The three proper servers are modern top spec kit with similar processors - the standalone having the slowest clock speed
 
The hardware stacks up like this:
Server 1 - current:
x32 sql 2000 4 x quad xeon 2.9Mhz 32gb ram, san attached, raid 10 data ( 10 x 15k ), raid 1 logs ( 15k )
Server 2 - new:
x64 sql 2005 4 x quad xeon 2.9Mhz 64gb ram, virtualised san
Server 3 - standalone:
x64 sql2005 4 x quad xeon 2.4Mhz 32gb ram, raid 10 data ( 4 x 10k sas ), raid 1 logs ( 10k sas )
Server 4 - home:
x64 sql 2008 1 x dual core amd 4200 8gb ram, hardware raid 0 data ( 4 x 7.2k sata ), single 7.2k sata logs.
 
So on a basic test of inserting 1 million rows into a table ( has 4 secondary indexes and a text column ) generating 8.5 Gb of data where would you rank the  
servers?     Vote now on 087xxxxxx etc. etc.
 
Average results of several runs:- ( single table test )
 
Server 3     6 mins 30 secs            2,564 rows/sec   22Mb/sec  1,330Mb/min
Server 4     9 mins                    1,851 rows/sec   16Mb/sec     961Mb/min
Server 1     11 mins                   1,515 rows/sec   13Mb/sec     786Mb/min
Server 2     17 mins                     980 rows/sec   8.5Mb/sec    509Mb/min
 
( I'm currently putting a doc together on all the tests including the code )

Posted by GrumpyOldDBA with 2 comment(s)
Filed under: , , ,