Ttdfjt

Does norwegian.no airline overbook flights?

In the MCU, why does Mjölnir retain its enchantments after Ragnarok?

Can't stopover at Sapporo when going from Asahikawa to Chitose airport?

Please help me identify the bold slashes between staves

Would this system work to purify water?

Is it safe to remove the bottom chords of a series of garage roof trusses?

Architectural feasibility of a tiered circular stone keep

Science fiction short story where aliens contact a drunk about Earth's impending destruction

How do I request a longer than normal leave of absence period for my wedding?

Did it used to be possible to target a zone?

Which household object drew this pattern?

Was there ever a treaty between 2 entities with significantly different translations to the detriment of one party?

If the first law of thermodynamics ensures conservation of energy, why does it allow systems to lose energy?

Why were the crew so desperate to catch Truman and return him to Seahaven?

Fancy String Replace

Non-visual Computers - thoughts?

Which note goes on which side of the stem?

What does どうかと思う mean?

LeetCode: Pascal's Triangle C#

What is this symbol: semicircles facing eachother

Is “I am getting married with my sister” ambiguous?

Is there any way to keep a player from killing an NPC?

Does travel insurance for short flight delays exist?

Sun setting in East!

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?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 serverCPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server?SQL Server CPU usage is overloaded sometimesSQL Server Failover Cluster Instances: why current vote is 0?automatic soft NUMA results in odd number of coresSQL 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;

3

I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned into.

The problem is that sometimes one or more of the instances are using all of the CPUs and then about 4 or 5 instances are out of CPU resources and failing.

What is the best way to spread CPU among all of the instances so each instance has some at the minimum?

sql-server cpu

share|improve this question

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

asked Aug 10 at 19:19

user3213459user3213459

1911 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
  Aug 10 at 19:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, agree but unfortunately i can' modify anything right now and i need to do something with the current design.
  
  – user3213459
  Aug 10 at 19:22
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Aug 10 at 19:29
  

  
  
  
  

add a comment | 

3

I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned into.

The problem is that sometimes one or more of the instances are using all of the CPUs and then about 4 or 5 instances are out of CPU resources and failing.

What is the best way to spread CPU among all of the instances so each instance has some at the minimum?

sql-server cpu

share|improve this question

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

asked Aug 10 at 19:19

user3213459user3213459

1911 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
  Aug 10 at 19:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks, agree but unfortunately i can' modify anything right now and i need to do something with the current design.
  
  – user3213459
  Aug 10 at 19:22
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Aug 10 at 19:29
  

  
  
  
  

add a comment | 

I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned into.

The problem is that sometimes one or more of the instances are using all of the CPUs and then about 4 or 5 instances are out of CPU resources and failing.

What is the best way to spread CPU among all of the instances so each instance has some at the minimum?

sql-server cpu

share|improve this question

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

asked Aug 10 at 19:19

user3213459user3213459

1911 bronze badge

share|improve this question

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

asked Aug 10 at 19:19

user3213459user3213459

1911 bronze badge

share|improve this question

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

asked Aug 10 at 19:19

user3213459user3213459

1911 bronze badge

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

edited Aug 12 at 1:32

Michael Green

15.8k88 gold badges3333 silver badges7070 bronze badges

asked Aug 10 at 19:19

user3213459user3213459

1911 bronze badge

asked Aug 10 at 19:19

user3213459user3213459

1911 bronze badge

1 Answer
1

active

oldest

votes

4

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 Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, do you think using Resource Governor is the better solution in this case?
  
  – user3213459
  Aug 10 at 22:00
  

  
  
  
  


• 
  
  
  

  
  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
  Aug 10 at 23:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much!!!
  
  – user3213459
  Aug 10 at 23:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Aug 11 at 0:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would recommend keeping an instance on a single NUMA node if you can.
  
  – Mr.Brownstone
  Aug 11 at 8:19
  

  
  
  
  

 | 
show 2 more comments

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

4

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 Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, do you think using Resource Governor is the better solution in this case?
  
  – user3213459
  Aug 10 at 22:00
  

  
  
  
  


• 
  
  
  

  
  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
  Aug 10 at 23:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much!!!
  
  – user3213459
  Aug 10 at 23:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Aug 11 at 0:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would recommend keeping an instance on a single NUMA node if you can.
  
  – Mr.Brownstone
  Aug 11 at 8:19
  

  
  
  
  

 | 
show 2 more comments

4

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 Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you, do you think using Resource Governor is the better solution in this case?
  
  – user3213459
  Aug 10 at 22:00
  

  
  
  
  


• 
  
  
  

  
  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
  Aug 10 at 23:08
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you so much!!!
  
  – user3213459
  Aug 10 at 23:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  Aug 11 at 0:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I would recommend keeping an instance on a single NUMA node if you can.
  
  – Mr.Brownstone
  Aug 11 at 8:19
  

  
  
  
  

 | 
show 2 more comments

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 Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges

share|improve this answer

answered Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges

answered Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges

answered Aug 10 at 19:28

Mr.BrownstoneMr.Brownstone

10.5k33 gold badges2626 silver badges4646 bronze badges