SQL Server Resource Governor - Boise SQL Server Users Group

SQL Server Resource Governor

Introduction To The Resource Governor • Resource Governor was added in SQL Server 2008 • Purpose is to manage resources by specifying limits on consumption of those resources by the requests processes that are using them. • Resource Governor is available in Enterprise, Developer, and Evaluation Editions of SQL Server 2008 and greater

What A Resource Governor Will Address • Intrusive queries that consume a lot of the resources • Unpredictable workload due to a workload type mixture. For example OLTP & DSS running on the same instance • Workload priority can be set to ensure higher priority processes get more resources

Resource Governor Provides • The ability to classify connections and route the workload to a specific group • The ability to monitor resource usage for each workload in a group • The ability to pool resources and set pool specific limits on CPU and memory. • The ability to associate grouped workloads with a specific pool of resources • The ability to set priorities for workloads

Resource Governor Limitations • Limited to SQL Server Engine Only • Self contained within a SQL Server. In other words, one instance doesn’t know about resource usage in another instance • Applies to only CPU and memory usage only • OLTP workloads are typically short in duration and can skew statistics.

High Level View Of Resource Governor (from Books Online)

3 Important Concepts • Resource Pools • Workload Groups • Classification

Resource Pools • Represents physical resources of a database instance • Types Of Pools – internal pool (can not be altered, created by default) – default pool (first predefined user pool, can not be dropped, but can be altered, created by default) – user defined pool (Can create up to 18)

• Two Parts – Minimum resource reservation - Minimum guaranteed availability of the pool – Maximum resource reservation – Maximum size of the pools

• The sum of MIN values across all pools can not exceed 100% of the instance resources • The MAX values can be set anywhere between MIN and 100%

Resource Pools (continued) Pool name

internal

MIN % setting

MAX % setting

0

100

Calculated Calculated effective MAX shared % %

100

0

Comment

Effective MAX % and shared % are not applicable to the internal pool.

default

0

100

25

The effective MAX value is calculated as: min(100,100-(20+50+5)) = 25. The 25 calculated shared % is Effective MAX MIN = 25.

Pool 1

20

100

45

The effective MAX value is calculated as: 25 min(100,100-55) = 45. The calculated Shared % is Effective MAX - MIN = 25.

Pool 2

50

70

70

The effective MAX value is calculated as: 20 min(70,100-25) = 70. The calculated Shared % is effective MAX - MIN = 20.

Pool 3

5

100

30

The effective MAX value is calculated as: 25 min(100,100-70) = 30. The calculated Shared % is effective MAX - MIN = 25.

Workload Groups • A container for session requests that are similar to how they are classified. This is where the policy is defined for all members of the group. • Types Of Groups – Internal – created by default and can not be modified – Default – group where members are placed by default under the following conditions • No classification for a request • Attempt to classify into a non-existent group • Any generic classification failure

• What can be controlled by a workload group – – – – – –

Maximum amount of memory for a request Maximum percentage of CPU for use Resource time-out for a request Priority Maximum number of requests Maximum degree of parallelism

Classification • Based on rules that are part of a function that classify a connection into a workload group. You can not classify anything into the internal workload group. • Steps To Get Classification Working – Write a scalar function that has the logic that assigns the session to a workload group – Register the function using the alter resource governor statement – Update the resource governor with the reconfigure option

Classification Function Requirements • Must be a scalar function (one and only one result) • Evaluated for every new session • Once workload group membership is determined, the connection is bound to that group for the its lifetime • Function must be defined in the master database • Only one function can be used by the resource governor at a time • If the function is dropped, then all sessions are bound to the default group. • Any connection using the Dedicated Admin Connection is not subject to the resource governor.

Resource Governor Monitoring • System views sys.resource_governor_configuration

Returns the stored Resource Governor state.

sys.resource_governor_resource_pools

Returns the stored resource pool configuration. Each row of the view determines the configuration of a pool.

sys.resource_governor_workload_groups

Returns the stored workload group configuration.

• Dynamic Management Views sys.dm_resource_governor_workload_groups

Returns workload group statistics and the current in-memory configuration of the workload group.

sys.dm_resource_governor_resource_pools

Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.

sys.dm_resource_governor_configuration

Returns a row that contains the current in-memory configuration state for Resource Governor.

Resource Governor Monitoring Continued • Perfmon Counters SQLServer:Workload Group Stats

Reports statistics for each active workload group, such as the number of active requests and the number of blocked requests.

SQLServer:Resource Pool Stats

Reports statistics for each active resource pool, such as the number of memory grants that are occurring in the resource pool per second and the amount of memory that is used by the resource pool.

Demonstration