Disabling Resource Governor in SQL ServerLimit CPU usage without Resource GovernorTroubleshooting Resource...
Disabling Resource Governor in SQL Server
Is balancing necessary on a full-wheel change?
Is it the same airport YUL and YMQ in Canada?
Floor tile layout process?
A non-technological, repeating, phenomenon in the sky, holding its position in the sky for hours
How did Captain America use this power?
I’ve officially counted to infinity!
How do I tell my manager that his code review comment is wrong?
What is the most remote airport from the center of the city it supposedly serves?
If 1. e4 c6 is considered as a sound defense for black, why is 1. c3 so rare?
Historically, were women trained for obligatory wars? Or did they serve some other military function?
CRT Oscilloscope - part of the plot is missing
Pressure to defend the relevance of one's area of mathematics
If Melisandre foresaw another character closing blue eyes, why did she follow Stannis?
Why is the SNP putting so much emphasis on currency plans?
I caught several of my students plagiarizing. Could it be my fault as a teacher?
Visa for volunteering in England
Write to EXCEL from SQL DB using VBA script
How to assert on pagereference where the endpoint of pagereference is predefined
Feels like I am getting dragged into office politics
How can I close a gap between my fence and my neighbor's that's on his side of the property line?
Junior developer struggles: how to communicate with management?
What word means "to make something obsolete"?
Was the ancestor of SCSI, the SASI protocol, nothing more than a draft?
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 2 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 2 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 2 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 2 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 2 hours ago
Joe ObbishJoe Obbish
22.5k43494
answered 2 hours ago
Joe ObbishJoe Obbish
22.5k43494
answered 2 hours ago
Joe ObbishJoe Obbish
22.5k43494
answered 2 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
