Microsoft SQL Server Performance Top Tip: Multi Processor (SMP) sudden death syndrome.
You’ve been really proud of your nice new multi processor box, its got eight processors and some serious grunt. Everyone has been really pleased with the system’s lightening responsiveness and the big boss thinks you’re a super computer guru – looks like a pay rise is on its way.
Then, one Monday morning you’ve strolled into work only to find everyone wants your blood. Your super new box is going really slow – the application it hosts appears to have died! What’s gone wrong – you decide to take a quick look at the performance of the processors using either Task Manager or System Monitor. To your horror you see exactly the same processor usage you saw when you originally bought the thing; only one processor seems to be hard at work, the others are just idling.
When you witnessed this behavior before you consulted one of my previous Top Tips SQL Server Performance Top Tip: Multi Processor (SMP) Box Doesn’t Use All It’s Processors and that did the trick – but it won’t work this time!
Well my guess is the symptom is the same as before because you are nearly in exactly the same situation as before. My guess is you have had automatic database growth turned on for this database and indeed the database has in fact grown over the weekend. Assuming when you created the database you created it with all the database data files the same size, I would wager at least one of those files has now grown? If this is the case then I think your problem is you’ve lost ‘proportional fill’. Look at the following excerpt from Books OnLine (BOL).
From Books Online:
As data is written to the filegroup, Microsoft® SQL Server™ writes an amount proportional to the free space in the file to each file within the filegroup, rather than writing all the data to the first file until full and then writing to the next file.
As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to accommodate more data (provided that the database is set to grow automatically).
You can loose proportional fill because now you only have the one, newly expanded, file to write to; all the others are full! With only one file to write to, you are essentially only able to use one CPU; as per the previous Top Tip.
The moral of this story is: avoid automatic database growth on multi processor systems.
Comments
Anonymous
January 01, 2003
Today is one of those I days I miss Take Outs.Anonymous
January 01, 2003
PingBack from http://thomaslarock.com/2009/05/autogrow-apparently-means-i-dont-care/Anonymous
February 02, 2005
I was going to ask what happens when the database runs out of file space, but then I suppose in a large system with SMP you would have one file per logical disk, which you'd make the size of the disk.
Automatic growth is really only for development and test, I suppose. In production you need to monitor how full the files are and plan for additional disks when getting close to capacity. I'm not sure it's even possible to add an additional stripe to an existing striped set.
At my employer, a small ISV/VAR, we don't see a lot of serious enterprise hardware. At least, the developers don't get to see it - some of our customers have it. The nearest we've got lately are some HP ProLiant DL380 G4 boxes set up with three disks in a RAID-5 configuration. It sounds like that customer might well run into the problem you describe, although the first thing they'll need to do is put different databases on different spindles and the transaction logs on different spindles again.Anonymous
February 03, 2005
The comment has been removedAnonymous
February 04, 2005
Automatic growth is also good for low Maintenance databases - typically MSDE applications that only run with one processorAnonymous
February 04, 2005
Parallelism - I'll post a blog on this sometime soon