Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How To Configure SQL Server Processor Use?

Posted on 2013-12-03
Medium Priority
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 600 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 400 total points
ID: 39694638
Ummm... Affinity mask is set to disappear at some stage.

Read : http://technet.microsoft.com/en-us/library/ms187104(v=sql.105).aspx

as for "how to" : http://technet.microsoft.com/en-us/library/ms186255(v=sql.105).aspx

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 : http://www.brentozar.com/archive/2012/12/dont-touch-that-button-four-dangerous-settings-in-sql-server-video/ 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 1000 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.
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.


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 http://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx.

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 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 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