August 30, 2011 at 10:52 am
Hi,
I have a development machine which has 16 GB of RAM with just 2 CPU's on it. I have around 15 databases hosted on that server out of which 2-3 of them are the most heavy resource consuming and it almost KILLS the application.Windows Performance Monitor clearly indicates a pressure on the server in terms of no of CPU's but still is there any documentation,tutorial, article which provides a more complete picture on How can we decide on the exact number of processors a SQL Server is in need of.
With Thanks,
Satnam
August 30, 2011 at 11:19 am
The reason there isn't a book or whatever on that is there isn't really a formula or rule on the subject.
The first thing to do is see if you can cut CPU use by optimizing code. Fairly often (not more than 99.999% of the time), code optimization will get better results than just throwing hardware at it, in this kind of situation.
Have you, for example, downloaded a free copy of Confio's Ignite product? That will tell you what the worst queries are, and you can start by optimizing those.
If you haven't analyzed the code for resource-hogging, all you're likely to do is add more CPUs, and then find out that those are slammed too, with small to no actual improvement. Happens all the time.
Beyond that, it's a question of analyzing wait times, making sure it really is CPU that's the bottleneck (I/O is more common), and then working out how much of an improvement you need to get performance where you want it. Then work out the price to get that improvement. Then shudder at the price, and see if optimizing code can do it better or not. And so on.
But the first step, either way, is to analyze resource waits. That'll tell you how much optimizing you need vs how much hardware.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply