September 12, 2002 at 1:39 pm
We are trying to spec a server to run multiple instances of Sql Server and I was curious if there is documentation somewhere that can help me decide when I should be adding processors (ex: 2 to 4). I believe this is dependent on load but any suggestions would be helpful.
Thank you in advance.
Hampsont
Trevor Hampson
DBA
September 12, 2002 at 2:26 pm
It's completely dependent on anticipated or actual load (new system vs. existing). The best thing to do is if you have an existing system, baseline it, see how the current system is performing. The baseline will give you something to extrapolate from.
If it's a major third party application, most of the major hardware vendors have sizers or personnel who can assist with sizing available. HP/Compaq has sizers for SQL Server for some of the more intensive apps such as PeopleSoft.
If you don't do a lot of baselining (here comes the shameless plug) my eBook, Start to Finish Guide to SQL Server Performance Monitoring, is designed to help you with that.
Let me throw in, before Andy does, to get plenty of memory since it's relatively cheap and can make a major difference in performance.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 12, 2002 at 3:01 pm
Agree with all of the above!
Andy
September 12, 2002 at 3:26 pm
Thank you for the help.
Hampsont
Trevor Hampson
DBA
September 14, 2002 at 10:12 pm
One more thing:
In high availability environments, the typical load is not of much importance. What is important is the amount of time SQL Server (or another process) tops the CPU at a 100%.
So although 99% of the time the server is running at 10% CPU capacity on 4 procs, the question becomes what happens to the remaining 1%? Is this a business critical report that clogs everything else for 20 minutes (then potentially add more procs) or is this a simple backup that runs at 2am when no one really cares?
Finally, when running multiple servers on the same box (say SQL Server and IIS), consider affinitizing the processors for IIS and SQL Server so that they do not conflict. A product such as Aurema's ARMTech may be able to help you in guaranteeing a minimum CPU availability for your servers. Check out http://www.aurema.com - it's worth it (and pretty cheap too).
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply