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 53

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.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


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 53

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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