Xjyuk

What is the idiomatic way of saying “he is ticklish under armpits”?

Was this a rapid SCHEDULED disassembly? How was it done?

If a Contingency spell has been cast on a creature, does the Simulacrum spell transfer the contingent spell to its duplicate?

Do other countries guarantee freedoms that the United States does not have?

Y2K... in 2019?

Why does Intel's Haswell chip allow FP multiplication to be twice as fast as addition?

Look mom! I made my own (Base 10) numeral system!

What word can be used to describe a bug in a movie?

Word or idiom defining something barely functional

Does a code snippet compile? Or does it get compiled?

Why isn’t SHA-3 in wider use?

How do I calculate the difference in lens reach between a superzoom compact and a DSLR zoom lens?

What does Apple mean by "This may decrease battery life"?

How to mark beverage cans in a cooler for a blind person?

Author changing name

Why couldn't soldiers sight their own weapons without officers' orders?

Could one become a successful researcher by writing some really good papers while being outside academia?

Want to draw this commutative diagram

Double blind peer review when paper cites author's GitHub repo for code

As a 16 year old, how can I keep my money safe from my mother?

During the Space Shuttle Columbia Disaster of 2003, Why Did The Flight Director Say, "Lock the doors."?

changing number of arguments to a function in secondary evaluation

Does two puncture wounds mean venomous snake?

Team goes to lunch frequently, I do intermittent fasting but still want to socialize

Best way to divide CPU along all sql instances on 1 server

Is it bad to assign specific processors to SQL Server 2008 R2 cluster instances?How to update SQL Server 2005 clustered instances?SQL Server 2012: NUMA node shown as offline without special affinity settingsRunning SQL Server on VMWare along with many other VM'sRecommended memory configuration for many SQL Server instances per serverBest way to merge tightly coupled SQL Server InstancesSQL Server CPU usage is overloaded sometimesSQL Server Failover Cluster Instances: why current vote is 0?SQL Server not using all NUMA memory with 20 core limit and affinity mask

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I have a physical server with 40 CPU's (hyperthreaded) with 2 numa nodes.
On this server i have 20 sql instances installed and i need to make sure each sql instance have some dedicated cpu assigned into.
The problem is that sometimes one or more of the instances are using all of the cpu's and then about 4 or 5 instances are out of CPU resources and failing.
What is the best way to spread cpu along all of the instances so each instance have some at the minimum?

sql-server cpu

share|improve this question

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

user3213459user3213459

1411 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The best way would be to have ONE instance and use the resource governor to make sure no instance is hogging the machine.
  
  – TomTom
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, agree but unfortunately i can' modify anything right now and i need to do something with the current design.
  
  – user3213459
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have an actual problem? because my second thought would be to do NOTHING - let windows handle the rare occasions where things matter.
  
  – TomTom
  8 hours ago
  

  
  
  
  

add a comment | 

2

I have a physical server with 40 CPU's (hyperthreaded) with 2 numa nodes.
On this server i have 20 sql instances installed and i need to make sure each sql instance have some dedicated cpu assigned into.
The problem is that sometimes one or more of the instances are using all of the cpu's and then about 4 or 5 instances are out of CPU resources and failing.
What is the best way to spread cpu along all of the instances so each instance have some at the minimum?

sql-server cpu

share|improve this question

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

user3213459user3213459

1411 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  The best way would be to have ONE instance and use the resource governor to make sure no instance is hogging the machine.
  
  – TomTom
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, agree but unfortunately i can' modify anything right now and i need to do something with the current design.
  
  – user3213459
  8 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you have an actual problem? because my second thought would be to do NOTHING - let windows handle the rare occasions where things matter.
  
  – TomTom
  8 hours ago
  

  
  
  
  

add a comment | 

I have a physical server with 40 CPU's (hyperthreaded) with 2 numa nodes.
On this server i have 20 sql instances installed and i need to make sure each sql instance have some dedicated cpu assigned into.
The problem is that sometimes one or more of the instances are using all of the cpu's and then about 4 or 5 instances are out of CPU resources and failing.
What is the best way to spread cpu along all of the instances so each instance have some at the minimum?

sql-server cpu

share|improve this question

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

user3213459user3213459

1411 bronze badge

share|improve this question

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

user3213459user3213459

1411 bronze badge

share|improve this question

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

user3213459user3213459

1411 bronze badge

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

edited 7 hours ago

Colin 't Hart

6,90388 gold badges2727 silver badges3434 bronze badges

asked 8 hours ago

user3213459user3213459

1411 bronze badge

asked 8 hours ago

user3213459user3213459

1411 bronze badge

1 Answer
1

active

oldest

votes

2

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity

Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.

If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.

share|improve this answer

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, do you think using Resource Governor is the better solution in this case?
  
  – user3213459
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  No because there is no workload management between SQL Server Instances and you would have to configure and manage it across each one you have. I think the affinity is probably your best way to go but you may have to tweak allocations until they are balanced correctly across your instances. Your other option is to configure the MAXDOP but this does not constrain SQL Server to specific CPU's it only constrains the amount that can be used for parallel queries and not everything is a parallel query.
  
  – Mr.Brownstone
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much!!!
  
  – user3213459
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  One more question please, let's say for 1 instance i decided to assign 4 cpu's , should i setup 2 for numa1 and 2 for numa2?
  
  – user3213459
  3 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f245065%2fbest-way-to-divide-cpu-along-all-sql-instances-on-1-server%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity

Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.

If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.

share|improve this answer

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, do you think using Resource Governor is the better solution in this case?
  
  – user3213459
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  No because there is no workload management between SQL Server Instances and you would have to configure and manage it across each one you have. I think the affinity is probably your best way to go but you may have to tweak allocations until they are balanced correctly across your instances. Your other option is to configure the MAXDOP but this does not constrain SQL Server to specific CPU's it only constrains the amount that can be used for parallel queries and not everything is a parallel query.
  
  – Mr.Brownstone
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much!!!
  
  – user3213459
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  One more question please, let's say for 1 instance i decided to assign 4 cpu's , should i setup 2 for numa1 and 2 for numa2?
  
  – user3213459
  3 hours ago
  

  
  
  
  

add a comment | 

2

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity

Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.

If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.

share|improve this answer

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, do you think using Resource Governor is the better solution in this case?
  
  – user3213459
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  No because there is no workload management between SQL Server Instances and you would have to configure and manage it across each one you have. I think the affinity is probably your best way to go but you may have to tweak allocations until they are balanced correctly across your instances. Your other option is to configure the MAXDOP but this does not constrain SQL Server to specific CPU's it only constrains the amount that can be used for parallel queries and not everything is a parallel query.
  
  – Mr.Brownstone
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much!!!
  
  – user3213459
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  One more question please, let's say for 1 instance i decided to assign 4 cpu's , should i setup 2 for numa1 and 2 for numa2?
  
  – user3213459
  3 hours ago
  

  
  
  
  

add a comment | 

You can configure the processor affinity used by an instance of SQL Server by right clicking the instance and choosing Properties -> Processors:

Processor Affinity

Assigns processors to specific threads to eliminating processor reloads and reduce thread migration across processors. For more information, see affinity mask Server Configuration Option.

This will enable you to assign specific processors per instance. Getting this right though requires constant attention and tuning so generally I don't advise it unless you really know what you are doing.

If you do decide to proceed, then I highly recommend reading about the affinity mask Server Configuration Option before you attempt it and also you can read a quick tutorial on TechNet and some basic guidelines can be found on Microsoft Support.

share|improve this answer

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges

share|improve this answer

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges

answered 8 hours ago

Mr.BrownstoneMr.Brownstone

10.4k33 gold badges2626 silver badges4646 bronze badges