SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
Resource Governor Constraints
This release of Resource Governor has the following constraints:
- Resource management is limited to the SQL Server Database Engine. Resource Governor can not be used for Analysis Services, Integration Services, and Reporting Services.
- There is no workload monitoring or workload management between SQL Server instances.
- Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. This may skew in the statistics returned for CPU usage %.
- The ability to govern physical IO only applies to user operations and not system tasks. System tasks include write operations to the transaction log and Lazy Writer IO operations. The Resource Govenor applies primarily to user read operations because most write operations are typically performed by system tasks.
- You cannot set IO thresholds on the internal resource pool.
The following three concepts are fundamental to understanding and using Resource Governor:
- Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance. Two resource pools (internal and default) are created when SQL Server is installed. Resource Governor also supports user-defined resource pools.
- Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server is installed. Resource Governor also supports user-defined workload groups.
- Classification. The Classification process assigns incoming sessions to a workload group based on the characteristics of the session. You can tailor the classification logic by writing a user-defined function, called a classifier function. Resource Governor also supports a classifier user-defined function for implementing classification rules.
Resource Pool Concepts
A resource pool, or pool, represents the physical resources of the server. You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance. A pool has two parts. One part does not overlap with other pools, which enables minimum resource reservation. The other part is shared with other pools, which supports maximum possible resource consumption. The pool resources are defined by specifying one or more of the following settings for each resource (CPU, memory, and physical IO):
- MIN_CPU_PERCENT and MAX_CPU_PERCENTThese settings are the minimum and maximum guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. You can use these settings to establish predictable CPU resource usage for multiple workloads that is based on the needs of each workload. For example, assume the Sales and Marketing departments in a company share the same database. The Sales department has a CPU-intensive workload with high-priority queries. The Marketing department also has a CPU-intensive workload, but has lower-priority queries. By creating a separate resource pool for each department, you can assign a minimum CPU percentage of 70 for the Sales resource pool and a maximum CPU percentage of 30 for the Marketing resource pool. This ensures that the Sales workload receives the CPU resources it requires and the Marketing workload is isolated from the CPU demands of the Sales workload. Note that the maximum CPU percentage is an opportunistic maximum. If there is available CPU capacity, the workload uses it up to 100 percent. The maximum value only applies when there is contention for CPU resources. In this example, if the Sales workload is switched off, the Marketing workload can use 100 percent of the CPU if needed.
- CAP_CPU_PERCENTThis settings is a hard cap limit on the CPU bandwidth for all requests in the resource pool. Workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT. Using the example above, lets assume that the Marketing department is being charged for their resource usage. They want predictable billing and do not want to pay for more than 30 percent of the CPU. This can be accomplished by setting the CAP_CPU_PERCENT to 30 for the Marketing resource pool.
- MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENTThese settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of overall memory.
- AFFINITYThis setting lets you affinitize a resource pool to one or more schedulers or NUMA nodes for greater isolation of CPU resources. Using the Sales and Marketing scenario above, lets assume that the Sales department needs a more isolated environment and wants 100 percent of a CPU core at all times. By using the AFFINITY option the Sales and Marketing workloads can be scheduled on different CPUs. Assuming the CAP_CPU_PERCENT on the Marketing pool is still in place, the Marketing workload continues to use a maximum of 30 percent of one core, while the Sales workload uses 100 percent of the other core. As far as the Sales and Marketing workloads are concerned, they are running on two isolated machines.
- MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUMEThese settings are the minimum and maximum physical IO operations per second (IOPS) per disk volume for a resource pool. You can use these settings to control the physical IOs issued for user threads for a given resource pool. For example, the Sales department generates several end-of-month reports in large batches. The queries in these batches can generate IOs that can saturate the disk volume and impact the performance of other higher priority workloads in the database. To isolate this workload, the MIN_IOPS_PER_VOLUME is set to 20 and the MAX_IOPS_PER_VOLUME is set to 100 for the Sales department resource pool, which controls the level of IOs that can issued for the workload.
Resource Governor supports the classification of incoming sessions. Classification is based on a set of user-written criteria contained in a function. The results of the function logic enable Resource Governor to classify sessions into existing workload groups.
The internal workload group is populated with requests that are for internal use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group.
You can write a scalar function that contains the logic that is used to assign incoming sessions to a workload group. Before you can use this function, you must complete the following actions:
- Create and register the function using the ALTER RESOURCE GOVERNOR statement.
- Update the Resource Governor configuration using the ALTER RESOURCE GOVERNOR statement with the RECONFIGURE parameter.
After you create the function and apply the configuration changes, the Resource Governor classifier will use the workload group name returned by the function to send a new request to the appropriate workload group.
The client session may time out if the classification function does not complete within the specified time-out for the login. Login time-out is a client property and as such, the server is unaware of a time-out. A long-running classifier function can leave the server with orphaned connections for long periods. It is important that you create classifier functions that finish executing before a connection time-out.
The user-defined function has the following characteristics and behaviors:
- The user-defined function is evaluated for every new session, even when connection pooling is enabled.
- The user-defined function gives workload group context for the session. After group membership is determined, the session is bound to the workload group for the lifetime of the session.
- If the user-defined function returns NULL, default, or the name of non-existent group the session is given the default workload group context. The session is also given the default context if the function fails for any reason.
- The function should be defined with server scope (master database).
- The classifier user-defined function designation only takes effect after ALTER RESOURCE GOVERNOR RECONFIGURE is executed.
- Only one user-defined function can be designated as a classifier at a time.
- The classifier user-defined function cannot be dropped or altered unless its classifier status is removed.
- In the absence of a classifier user-defined function, all sessions are classified into the default group.
- The workload group returned by the classifier function is outside the scope of the schema-binding restriction. For example, you cannot drop a table, but you can drop a workload group.
In the context of Resource Governor, the login process for a session consists of the following steps:
- Login authentication
- LOGON trigger execution
When classification starts, Resource Governor executes the classifier function and uses the value returned by the function to send requests to the appropriate workload group.
Enable Resource Governor
The Resource Governor is turned off by default. You can enable the Resource Governor by using either SQL Server Management Studio or Transact-SQL.
Enable Resource Governor Using Object Explorer
To enable the Resource Governor by using Object Explorer
- In SQL Server Management Studio, open Object Explorer and recursively expand the Management node down to Resource Governor.
- Right-click Resource Governor, and then click Enable.
Enable Resource Governor Using Transact-SQL
ALTER RESOURCE GOVERNOR RECONFIGURE; GO
--EXMAIN CURRENT CONFIGUARTION SELECT * FROM SYS.dm_resource_governor_configuration GO CREATE RESOURCE POOL MarketingPool with (max_cpu_percent=30) CREATE RESOURCE POOL DevelopmentPool with (max_cpu_percent=70) go select * from sys.dm_resource_governor_resource_pools GO ALTER RESOURCE GOVERNOR RECONFIGURE GO SELECT * FROM SYS.dm_resource_governor_resource_pools GO CREATE WORKLOAD GROUP MarketingGroup using MarketingPool GO CREATE WORKLOAD GROUP DevelopmentGroup using DevelopmentPool GO ALTER RESOURCE GOVERNOR RECONFIGURE GO SELECT * FROM SYS.dm_resource_governor_workload_groups GO IF DB_ID('MarketingDB') IS NULL CREATE DATABASE MarketingDB GO IF DB_ID('DevelopmentDB') IS NULL CREATE DATABASE DevelopmentDB GO if OBJECT_ID('dbo.myclassifier')>0 drop function dbo.myclassifier go CREATE FUNCTION dbo.myclassifier() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @GroupName SYSNAME IF ORIGINAL_DB_NAME()='MarketingDB' SET @GroupName='MarketingGroup' ELSE IF ORIGINAL_DB_NAME()='DevelopmentDB' SET @GroupName='DevelopmentGroup' ELSE SET @GroupName='Default' RETURN @GroupName; END GO ALTER RESOURCE GOVERNOR WITH(CLASSIFIER_FUNCTION=dbo.myclassifier) GO ALTER RESOURCE GOVERNOR RECONFIGURE
know what sessions where assigned to each workload groups
SELECT session_id as 'Session ID', [host_name] as 'Host Name', [program_name] as 'Program Name', nt_user_name as 'User Name', SDRGWG.[Name] as 'Group Assigned', DRGRP.[name] as 'Pool Assigned' FROM sys.dm_exec_sessions SDES INNER JOIN sys.dm_resource_governor_workload_groups SDRGWG ON SDES.group_id = SDRGWG.group_id INNER JOIN sys.dm_resource_governor_resource_pools DRGRP ON SDRGWG.pool_id = DRGRP.pool_id
Data is fascinating.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .
My areas of specialty are:
• SQL Server 2019-2008 performance tuning.
• Designing high availability and disaster recovery solutions.
• Securing SQL Server
• SSIS , SSAS
• Designing Data warehouse