Solved

How To Configure SQL Server Processor Use?

Posted on 2013-12-03
7
2,580 Views
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?
0
Comment
Question by:waltforbes
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 17

Assisted Solution

by:dbaSQL
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.  

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/28bb8423-5204-467e-9681-6da410ac8da2/how-to-configure-the-number-of-cpu-used-by-sql-server?forum=sqldatabaseengine
0
 
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 : 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...
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 39694763
Hi,

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)

HTH
  David

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.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:waltforbes
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)?
0
 
LVL 35

Assisted Solution

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

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.

Regards
  David
0
 

Author Closing Comment

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

Expert Comment

by:Mark Wills
ID: 39696734
Concur...

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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now