Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
add comment

CPU performance gains can be made by spreading tables across file groups, but it's kind of indirect.

When SQL Server wants to read data, it issues a requests and sleeps the thread making the request until the I/O hardware satisfies the request. If your query is across tables that are on separate physical storage, or are stored on tables that span multiple physical drives in the same file system, SQL Server can issue multiple I/O requests. This makes the I/O faster, which means the CPU will spend less time sleeping.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.