Disabling Resource Governor in SQL ServerLimit CPU usage without Resource GovernorTroubleshooting Resource...
Disabling Resource Governor in SQL Server
What happens if I start too many background jobs?
Is Cola "probably the best-known" Latin word in the world? If not, which might it be?
How to scale a verbatim environment on a minipage?
Applying a function to a nested list
How could a planet have most of its water in the atmosphere?
Was Hulk present at this event?
Is balancing necessary on a full-wheel change?
What are the spoon bit of a spoon and fork bit of a fork called?
What happened to Ghost?
Can PCs use nonmagical armor and weapons looted from monsters?
Selecting a secure PIN for building access
Why was Germany not as successful as other Europeans in establishing overseas colonies?
A non-technological, repeating, phenomenon in the sky, holding its position in the sky for hours
Why is Thanos so tough at the beginning of "Avengers: Endgame"?
How to implement float hashing with approximate equality
Can I use 1000v rectifier diodes instead of 600v rectifier diodes?
If Melisandre foresaw another character closing blue eyes, why did she follow Stannis?
How to reply this mail from potential PhD professor?
When do aircrafts become solarcrafts?
Why do freehub and cassette have only one position that matches?
Is it always OK to ask for a copy of the lecturer's slides?
Stark VS Thanos
Can fracking help reduce CO2?
Disabling Resource Governor in SQL Server
Limit CPU usage without Resource GovernorTroubleshooting Resource Governor / Classification FunctionSql Server 2014 IO Resource GovernorSQL Server 2014, Volumes in Resource GovernorDoes Resource Governor help protect page life expectancy?Resource Governor seems to increase resource usageHundreds of sessions queued in suspended stateSQL Server Resource governor user-defined resource poolsResource Governor not limiting CPU / RAMUsing Resource Governor with SQL Agent Jobs
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Should we restart SQL Server after disabling Resource Governor? What is the best practice?
Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their behavior change(in terms of CPU, Memory and IO) to default if we only disable Resource Governor and not restart SQL Services?
For example if a particular session was capped to use 30% of total memory of system because of resource governor. So if we disable Resource governor now alone, will it start using more than 30% instantaneously?
sql-server resource-governor
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
add a comment |
Should we restart SQL Server after disabling Resource Governor? What is the best practice?
Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their behavior change(in terms of CPU, Memory and IO) to default if we only disable Resource Governor and not restart SQL Services?
For example if a particular session was capped to use 30% of total memory of system because of resource governor. So if we disable Resource governor now alone, will it start using more than 30% instantaneously?
sql-server resource-governor
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
add a comment |
Should we restart SQL Server after disabling Resource Governor? What is the best practice?
Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their behavior change(in terms of CPU, Memory and IO) to default if we only disable Resource Governor and not restart SQL Services?
For example if a particular session was capped to use 30% of total memory of system because of resource governor. So if we disable Resource governor now alone, will it start using more than 30% instantaneously?
sql-server resource-governor
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
Should we restart SQL Server after disabling Resource Governor? What is the best practice?
Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their behavior change(in terms of CPU, Memory and IO) to default if we only disable Resource Governor and not restart SQL Services?
For example if a particular session was capped to use 30% of total memory of system because of resource governor. So if we disable Resource governor now alone, will it start using more than 30% instantaneously?
sql-server resource-governor
sql-server resource-governor
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
asked 4 hours ago
sachin-SQLServernewbieesachin-SQLServernewbiee
548
548
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:
- Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
- I have never seen query plan compilation account for Resource Governor settings.
- Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.
A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":
- MIN_CPU_PERCENT
- MAX_CPU_PERCENT
- CAP_CPU_PERCENT
- MIN_IOPS_PER_VOLUME
- MAX_IOPS_PER_VOLUME
- IMPORTANCE
- REQUEST_MAX_CPU_TIME_SEC
- GROUP_MAX_REQUESTS
and all of these to update only after the next query starts executing:
- MIN_MEMORY_PERCENT
- MAX_MEMORY_PERCENT
- AFFINITY
- REQUEST_MAX_MEMORY_GRANT_PERCENT
- REQUEST_MEMORY_GRANT_TIMEOUT_SEC
- MAX_DOP
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
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%2f236976%2fdisabling-resource-governor-in-sql-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
I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:
- Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
- I have never seen query plan compilation account for Resource Governor settings.
- Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.
A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":
- MIN_CPU_PERCENT
- MAX_CPU_PERCENT
- CAP_CPU_PERCENT
- MIN_IOPS_PER_VOLUME
- MAX_IOPS_PER_VOLUME
- IMPORTANCE
- REQUEST_MAX_CPU_TIME_SEC
- GROUP_MAX_REQUESTS
and all of these to update only after the next query starts executing:
- MIN_MEMORY_PERCENT
- MAX_MEMORY_PERCENT
- AFFINITY
- REQUEST_MAX_MEMORY_GRANT_PERCENT
- REQUEST_MEMORY_GRANT_TIMEOUT_SEC
- MAX_DOP
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
add a comment |
I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:
- Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
- I have never seen query plan compilation account for Resource Governor settings.
- Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.
A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":
- MIN_CPU_PERCENT
- MAX_CPU_PERCENT
- CAP_CPU_PERCENT
- MIN_IOPS_PER_VOLUME
- MAX_IOPS_PER_VOLUME
- IMPORTANCE
- REQUEST_MAX_CPU_TIME_SEC
- GROUP_MAX_REQUESTS
and all of these to update only after the next query starts executing:
- MIN_MEMORY_PERCENT
- MAX_MEMORY_PERCENT
- AFFINITY
- REQUEST_MAX_MEMORY_GRANT_PERCENT
- REQUEST_MEMORY_GRANT_TIMEOUT_SEC
- MAX_DOP
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
add a comment |
I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:
- Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
- I have never seen query plan compilation account for Resource Governor settings.
- Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.
A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":
- MIN_CPU_PERCENT
- MAX_CPU_PERCENT
- CAP_CPU_PERCENT
- MIN_IOPS_PER_VOLUME
- MAX_IOPS_PER_VOLUME
- IMPORTANCE
- REQUEST_MAX_CPU_TIME_SEC
- GROUP_MAX_REQUESTS
and all of these to update only after the next query starts executing:
- MIN_MEMORY_PERCENT
- MAX_MEMORY_PERCENT
- AFFINITY
- REQUEST_MAX_MEMORY_GRANT_PERCENT
- REQUEST_MEMORY_GRANT_TIMEOUT_SEC
- MAX_DOP
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:
- Once a session is assigned to a Resource Governor workload group it stays there, even if the classifier function changes.
- I have never seen query plan compilation account for Resource Governor settings.
- Some aspects of query execution are decided at the start of query execution, such as MAXDOP and query memory. Note that there are some exceptions where that isn't always true such as certain types of index creation or queries that execute in batch mode.
A precise answer that fully answers when the changes are applied to queries (whether or not it is "instantaneous") is likely outside the scope of an answer here. I suggest figuring out what you care about and doing testing as needed to convince yourself that what SQL Server is doing is okay with your workload. For example, consider the MAXDOP limit available through Resource Governor workload group configuration. For nearly all queries MAXDOP is decided at query execution time, so I wouldn't expect disabling Resource Governor to change the MAXDOP of a currently executing query. However, the next query that starts for the session will no longer be limited in MAXDOP. However, what happens if there's a Scalar UDF executing many times during a query with a parallel plan? I'd expect MAXDOP to possibly change for that query, but I've never tested that and don't care to test it. If I had to guess, I suspect that all of the following will be updated "instantaneously":
- MIN_CPU_PERCENT
- MAX_CPU_PERCENT
- CAP_CPU_PERCENT
- MIN_IOPS_PER_VOLUME
- MAX_IOPS_PER_VOLUME
- IMPORTANCE
- REQUEST_MAX_CPU_TIME_SEC
- GROUP_MAX_REQUESTS
and all of these to update only after the next query starts executing:
- MIN_MEMORY_PERCENT
- MAX_MEMORY_PERCENT
- AFFINITY
- REQUEST_MAX_MEMORY_GRANT_PERCENT
- REQUEST_MEMORY_GRANT_TIMEOUT_SEC
- MAX_DOP
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
answered 3 hours ago
Joe ObbishJoe Obbish
22.5k43494
22.5k43494
add a comment |
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%2f236976%2fdisabling-resource-governor-in-sql-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
