5

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?

Improve this question
0
7

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.