How To Configure SQL Server Processor Use?

Posted on 2013-12-03
Last Modified: 2013-12-04
Points of My Scenario
1. I am admin of a SQL 2008 R2 member machine on Server 2008 R2 SP1
2. My processor config is 2 sockets of 6-core Intel Xeon X5690 at 3.47GHz, 12 threads per socket.
3. In Task Manager, the processor affinity for "sqlservr.exe" is set to "All Processors" and shows 12 "CPU" 's per node (a total of 24 CPU's) - ALL checked.
4. Memory use is on average 8GB out of a total of 32GB
5. I want to ensure that SQL Server is using all available processing power - to maximize performance.

Question:  Is there a SQL command or other SQL configuration necessary to ensure that SQL Server process uses all processor cores and threads?
Question by:waltforbes
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 17

Assisted Solution

dbaSQL earned 150 total points
ID: 39694577
Please take a look here, from the informative queries at the top, all the way to the use of affinity mask to configure the processors, nearly at the bottom.
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 39694638
Ummm... Affinity mask is set to disappear at some stage.

Read :

as for "how to" :

And there are good SQL scripts in the above link that can show what is going on. But it does not necessarily say that one CPU is being overworked and another is idle.

This is getting into the realm of very serious fine tuning... and should not be undertaken lightly.

Not that if you manually set Affinity or Affinity64 you must set the bits the right way.

Affinity and IO affinity are like a toggle. If one of them is set (ie a 1) then the corresponding other cannot be. But they can both be zero.

Might be worth watching : it is about 11:30 into the video... And there is a link under where MS Customer Support reckons it has only helped in limited cases (he cites 6 cases in all his experience).

So, while you "can" do it, you need to fully understand the implications...
LVL 35

Accepted Solution

David Todd earned 250 total points
ID: 39694763

I think that you are asking the wrong question for your intent.

I think you should be looking at the degrees of parallelism (DOP) setting. That is, if you have a few overnight processors that are really really big, then if DOP is set to 1 (or forced on key parts of the query) then you are going to have one thread/process in SQL, which means one core will be used.

So, what is Max Degree of Parallelism set to? (In Object Explorer, right click on the SQL instance, properties, Advanced tab, second to last setting)


PS About the processor masks - I agree with Mark. Fiddling with them is likely to yield little to no gain at best. That is, (no offence intended) but if you have to ask the question here then you don't know enough to derive useful benefit. Its the sort of question the real SQL experts like MCP's and MCM ask.
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.


Author Comment

ID: 39695963
To dbaSQL: those are excellent queries! I got the exact info I was looking for.

To mark_wills: thanks for the in-depth insights. I agree that processor affinity masking is for limited use. I will stay away from it since task manager shows light processor activity.

To David: Great insight! "Max Degree of Parallelism" is set to 0 (zero), which indicates all processors are in use ("Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors") according to

Based on the following, I conclude that I should not change anything:
1. The setting "Automatically set processor affinity mask for all processors" is enabled
2. The setting "Automatically set I/O affinity mask for all processors" is enabled
3. The setting "Max Degree of Parallelism" is set to zero (0)
4. Task manager consistently shows low processor use
5. All processors are accounted for in the query [select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE']

QUESTION: Which of the 5 points above are valid for my conclusion (Is my conclusion even valid)?
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 39696129

I haven't used that dmv that you refer to so I can't comment on point 5.

But when I performed an upgrade/migration from a 2 socket server, to a 2 socket server with multiple cores and then seeing 24 cpu's in task manager, it was hard to find anything to stress the server enough to engage all the cpu's.

So I concur with points 1 - 4 and for point 5 I don't know.


Author Closing Comment

ID: 39696646
You all contributed to the whole picture; and now, I am wholly satisfied! :-)
LVL 51

Expert Comment

by:Mark Wills
ID: 39696734

All 5 points are valid.

It is rare for CPU to go ballistic unless you have several competing tasks.

Then the FIRST thing to do is to monitor the servers and gather empirical data / statistics and deal with them as required.

Surprisingly enough, sometimes it is very easy to fix with some good old fashioned management. Make sure Database is kept "clean" with correct size and all the various backups and periodic housekeeping such as indexing / defragmenting and expensive queries.

Your machine with 8meg of 32 and low CPU doesn't sound stretched at all. SQL is quite memory hungry so that is one aspect to keep an eye out for.

You might want to set max memory in SQL config.

Affinity and Affinity64, maxdop, working threads, resource manager settings are only really applicable when things are really getting pear shaped.

Well, one small exception, if working on a numa machine hyperthreading can sometimes create some issues, but still, it has to be a pretty heavily loaded machine and yours doesn't seem to be.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question