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.