Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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.
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.


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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

564 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