We have a SQL Server 2008 R2 (10.5) on a dedicated box with a single 2.4Ghz processor, which regularly runs at 70-80% CPU. We are going to be adding a significant number of users to the application and therefore want to add a second processor to the box (scale up).
Will SQL Server automatically use the second processor to balance threads, or is there additional configuration that will need to be done? In other words, will adding the second processor drop my CPU usage to 35-40% per CPU, automatically balancing the load?
Based on what I read here, it seems that it will: http://msdn.microsoft.com/en-us/library/ms181007.aspx
However, I've read elsewhere that CPU performance gains can be made by assigning database tables to different filegroups, but I'm not sure we want to get that complicated at this point.
Best Answer
The link you provided is for Max Degree of Parallelism. That feature is completely different from what you are asking about.
SQL Server will use the new CPU added to your server as long as the operating system can see it, and as long as the "Affinity Mask" is set to 0 within SQL Server. You need to run
EXEC master.dbo.sp_configure 'affinity mask'from a query window and look at the results of the config_value. As long as it's 0, then SQL will automatically use the new CPU. However, before adding that new CPU, you first need to look at the edition of SQL that you're running. The max number of CPUs supported differs by edition.The link below will outline the limits of each one. If you don't know which edition you have, then you can run
SELECT SERVERPROPERTY('Edition')from that same query to find out.Maximum Number of Processors Supported by the Editions of SQL Server 2008 R2