Best way to divide CPU along all sql instances on 1 serverIs 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
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;
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
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
asked 8 hours ago
user3213459user3213459
141 bronze badge
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
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
asked 8 hours ago
user3213459user3213459
141 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
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
asked 8 hours ago
user3213459user3213459
141 bronze badge
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
sql-server cpu
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
asked 8 hours ago
user3213459user3213459
141 bronze badge
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
asked 8 hours ago
user3213459user3213459
141 bronze badge
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
edited 7 hours ago
Colin 't Hart
6,9038 gold badges27 silver badges34 bronze badges
6,9038 gold badges27 silver badges34 bronze badges
asked 8 hours ago
user3213459user3213459
141 bronze badge
asked 8 hours ago
user3213459user3213459
141 bronze badge
asked 8 hours ago
user3213459user3213459
141 bronze badge
141 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 |
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
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
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
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
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 |
1 Answer
1
active
oldest
votes
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.
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 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
);
);
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 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.
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 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.
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 bronze badges
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.
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 bronze badges
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 bronze badges
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 bronze badges
answered 8 hours ago
Mr.BrownstoneMr.Brownstone
10.4k3 gold badges26 silver badges46 bronze badges
10.4k3 gold badges26 silver badges46 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 |
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
Thank you, do you think using Resource Governor is the better solution in this case?
– user3213459
5 hours ago
Thank you, do you think using Resource Governor is the better solution in this case?
– user3213459
5 hours ago
1
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
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
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
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 |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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