Dela via

  • Artikel

Resource Governor States

Resource Governor has two states on SQL Server: enabled or disabled. These states affect basic operations as well as workload processing when you make configuration changes. This topic covers the Resource Governor states and what happens when:

  • The initial configuration and state when Resource Governor is installed.

  • You enable or disable Resource Governor.

  • You change a Resource Governor configuration.

Initial Configuration

When Resource Governor is installed, the following workload groups and their corresponding resource pools are created:

  • Internal group and Internal pool.

  • Default group and Default pool.

At this point Resource Governor is disabled.

Enable or Disable Resource Governor

The following behaviors are normal when you enable or disable resource governor.

Enable Resource Governor

To enable Resource Governor, execute ALTER RESOURCE GOVERNOR RECONFIGURE. Enabling Resource Governor has the following results:

  • The classifier function is executed for new connections so their workload can be assigned to workload groups.

  • The resource limits specified in the Resource Governor configuration are honored and enforced.

  • Requests that existed before enabling Resource Governor are affected by any configuration changes made when Resource Governor was disabled.

Disable Resource Governor

To disable Resource Governor, execute ALTER RESOURCE GOVERNOR DISABLE. Disabling Resource Governor has the following results:

  • The classifier function is not executed.

  • All new connections are automatically classified into the Default group.

  • System-initiated requests are classified into the Internal workload group.

  • All existing workload group and resource pool settings are reset to their default values. In this case no events are fired when limits are reached.

  • Normal system monitoring is not affected.

  • The Resource Governor configuration can be changed when Resource Governor is disabled, but changes do not take effect until Resource Governor is enabled.

  • Upon restarting SQL Server, the Resource Governor will not load its configuration but instead will have only the default and internal groups and pools.

Changing the Resource Governor Configuration

Resource Governor configuration changes do not take effect immediately. A configuration change happens in two steps:

  1. Issue the appropriate ALTER statement to change a resource pool or workload group setting. This updates the configuration metadata.

  2. Issue the ALTER RESOURCE GOVERNOR statement. This copies the updated metadata to the in-memory configuration. The following table describes when general configuration changes take effect and are visible in a Resource Governor session.

Component

Description

User-defined classification

The change is effective for new session connections.

Resource pool

Long-running sessions are likely to be affected by the change; short-running sessions may finish before the change takes effect.

Workload group

The current sessions are not affected by the change but new connections are affected.

In addition to the general session behavior when Resource Governor DDL statements are issued, individual settings in a resource pool or workload group have predictable results and a specific effect on processing when you change these settings.

Resource Pool Settings

There are four settings for configuring a resource pool. These settings are:

  • A minimum and maximum percentage for CPU use.

  • A minimum and maximum percentage for memory use.

When you apply changes to a resource pool configuration, there is a transition period during which active or waiting requests are handled. The following table describes session requirements and session behavior when you apply changes to a resource pool setting.

Setting

Action

Description

Minimum percentage

Increase or decrease

  • Increase: Any other resource pools are reduced to their effective maximum settings. If all of the minimum settings add up to 100 percent, you have to decrease some of the minimum settings in the other resource pools and then increase the minimum setting of the current resource pool.

  • Decrease: If the effective maximum equals the minimum, unallocated memory is pulled from the resource pool and queued requests may fail. Allocated memory is not pulled from the resource pool until active requests finish.

Maximum percentage

Increase or decrease

  • Increase: No issues as a result of the setting change.

  • Decrease: If the new value is higher than the actual usage, there are no issues. If the new value is lower than the actual usage, there is a transition period. Existing requests complete before the change can be made, and new requests may start failing.

Workload Group Settings

There are seven settings for configuring a workload group. These settings are:

  • A maximum memory allocation for a request.

  • A maximum CPU time for a request.

  • A resource time-out for a request.

  • A relative importance setting for a request.

  • A workgroup limit for the number of requests.

  • A maximum degree of parallelism.

  • A specified resource pool.

Setting

Action

Description

Maximum memory

Increase or decrease

  • Increase. No effect on active queries, and these queries can obtain more memory if it is available.

  • Decrease. Active requests are unaffected, but new requests will have less memory.

Maximum CPU time

Increase or decrease

  • Increase. No effect active queries.

  • Decrease. Causes an event to be fired for a query that is above the limit, but the query continues to run.

Resource time-out

Increase or decrease

Queries that are already waiting in the queue are not affected. New queries use the new setting.

Importance

Increase or decrease

Only affects CPU distribution for queries in the workload group.

Maximum number of requests

Increase or decrease

  • Increase. No effect on existing queries.

  • Decrease. Existing queries are unaffected, but new queries wait and may time-out.

Maximum degree of parallelism

Increase or decrease

Only affects the compile and execution of new queries.

Specified resource pool

Change

Active requests continue to use the existing resource pool and its settings. New requests use the new pool and its settings.

Resource Governor Settings

In addition to changing a resource governor configuration or changing the current state of Resource Governor, you can use the ALTER RESOURCE GOVERNOR RECONFIGURE statement to create or change the classifier function.

When you register a new classifier function, you change the rules used to route new connections to a different workload group. This change does not affect queries that are already classified; it only affects new queries.