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?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
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;
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
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
asked Aug 10 at 19:19
user3213459user3213459
191 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 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
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
asked Aug 10 at 19:19
user3213459user3213459
191 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
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
asked Aug 10 at 19:19
user3213459user3213459
191 bronze badge
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
sql-server cpu
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
asked Aug 10 at 19:19
user3213459user3213459
191 bronze badge
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
asked Aug 10 at 19:19
user3213459user3213459
191 bronze badge
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
edited Aug 12 at 1:32
Michael Green
15.8k8 gold badges33 silver badges70 bronze badges
15.8k8 gold badges33 silver badges70 bronze badges
asked Aug 10 at 19:19
user3213459user3213459
191 bronze badge
asked Aug 10 at 19:19
user3213459user3213459
191 bronze badge
asked Aug 10 at 19:19
user3213459user3213459
191 bronze badge
191 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 |
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
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
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
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
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 |
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 Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 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
);
);
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 Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 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.
answered Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 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.
answered Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 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 Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 bronze badges
answered Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 bronze badges
answered Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 bronze badges
answered Aug 10 at 19:28
Mr.BrownstoneMr.Brownstone
10.5k3 gold badges26 silver badges46 bronze badges
10.5k3 gold badges26 silver badges46 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
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
Thank you, do you think using Resource Governor is the better solution in this case?
– user3213459
Aug 10 at 22:00
Thank you, do you think using Resource Governor is the better solution in this case?
– user3213459
Aug 10 at 22:00
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
Aug 10 at 23:08
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
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
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
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
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
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