Kdtyjb

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;
}

3

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

share|improve this question

asked 4 hours ago

sachin-SQLServernewbieesachin-SQLServernewbiee

548

add a comment | 

3

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

share|improve this question

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

share|improve this question

asked 4 hours ago

sachin-SQLServernewbieesachin-SQLServernewbiee

548

share|improve this question

asked 4 hours ago

sachin-SQLServernewbieesachin-SQLServernewbiee

548

share|improve this question

asked 4 hours ago

sachin-SQLServernewbieesachin-SQLServernewbiee

548

asked 4 hours ago

sachin-SQLServernewbieesachin-SQLServernewbiee

548

asked 4 hours ago

sachin-SQLServernewbieesachin-SQLServernewbiee

548

1 Answer
1

active

oldest

votes

5

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

share|improve this answer

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
});


}
});

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%2f236976%2fdisabling-resource-governor-in-sql-server%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5

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

share|improve this answer

answered 3 hours ago

Joe ObbishJoe Obbish

22.5k43494

add a comment | 

5

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

share|improve this answer

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

share|improve this answer

answered 3 hours ago

Joe ObbishJoe Obbish

22.5k43494

share|improve this answer

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