SQL Server - Using Resource Governor for greater control over server resources

Resource Governor is nothing more than a feature available from SQL Server 2008 Enterprise that gives us the ability to manage server workload and resource consumption by specifying the CPU and memory limits that requests received can be used according to a profile to be defined (from hostname, username, AD group, software used, etc.), preventing these users from having a major impact on the environment as a whole.

An important note to note is that Resource Governor has gained a major upgrade from SQL Server 2014, which is also able to control and manage the I / O load of incoming requests, allowing you to limit the maximum amount of IOPS used to the certain profiles.

Not only can you determine maximum quantities, Resource Governor can also be used to set minimum amounts of resources that will be used by the profile (Pool) and ensure that these defined quantities are always available for these sessions.

Resource Governor is often used to limit certain database access profiles and to prevent sessions from different instances from having to contend for server resources with each other. Some examples where the use of Resource Governor can be implemented:

• limit maximum IOPS usage of ad-hoc user queries
• limit maximum CPU usage of instance jobs
• limit the maximum amount of memory used on a specific system
• limit the maximum IOPS usage of users who are part of a given AD group
• define the maximum amount of parallelism of queries for a given profile

The inner workings of Resource Governor boil down to three main components (Classifier Function, Workload Group, and Resource Pool), and act as follows:

• A new connection is created and a request is made by this session (1 Session of n).
• The session is classified according to the criteria predefined by the Classifier function. These criteria can be username, AD group to which it belongs, system role or database role to which it belongs, name of the software used, etc.
• The session request is routed to a workload group according to the return of the sort function, for example, 4 Group.
• The workload group uses the resource pool to which it is associated, for example, Pool 2.
• The resource pool provides and limits the resources required by the application, for example, 3 Application.

Reference: English - Portuguese

The classification functions or Classifier Functions are functions created by the user in the “master” database, which will be applied automatically in all new connections made in the instance that has the Resource Governor enabled.

These functions will return the name of the workload group to be used by this new connection and from the logic implemented by it, which can use native functions in SQL Server such as HOST_NAME (), APP_NAME (), SUSER_NAME (), SUSER_SNAME (), IS_SRVROLEMEMBER () and IS_MEMBER () to determine criteria to classify the session and determined which workload group will be used when program_name like '% Management Studio%', for example.

For this reason, it is important to keep in mind that a poorly implemented or resource-intensive function to assign sessions can end up causing a major performance issue in the instance. For this reason, it is good practice to enable the ACD connection to allow connection in the instance bypassing the use of the classification function. If you do not do this and need

If you did not create a classification function, or did not assign the function to the Resource Governor or the function's return is “default”, NULL or the name of a group returned by the function does not exist, this session will be routed to the workload group "Default".

Note that only one sort function can be used by Resource Governor, and because the role is created using the WITH SCHEMABINDING parameter, it can only be removed or changed if you remove the role's association with Resource Governor using the command
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL).

Creation Example

Reference: English - Portuguese

Workload groups are like containers for requests with similar criteria. Each workload group is associated with a resource pool, and a resource pool can be associated with N workload groups.

The workload group of a session is defined through the Classifier function and it has some very important functions and limitations, such as defining the importance of requests that are part of the workload group.

Through parameter IMPORTANCE = {LOW | MEDIUM (Default) | HIGH}, you can set requests from this workload group to take precedence over connections from another workload group using the same resource pool (this parameter does not affect requests using another resource pool). This value is used to define the order of the request list of the same scheduler. If you have other schedulers available, 1 query with LOW importance and 1 query with HIGH importance, 2 queries can be executed concurrently.

The REQUEST_MAX_MEMORY_GRANT_PERCENT parameter specifies the maximum amount (in%) of memory that a connection can draw from the pool. Note that specifying this value for 0 will prevent queries using SORT and HASH JOIN from executing.

The REQUEST_MAX_CPU_TIME_SEC parameter allows you to define the maximum time, in seconds, that a query can execute (the default value is 0, ie no limit). If the request times out, a trace event will be generated. Until SQL Server version 2017 CU3, the session would not be interrupted when it reached this limit, but from this version this can be configured using the 2422 traceflag.

The MAX_DOP parameter allows defining the maximum amount of parallelism (Maximum Degree Of Parallelism - MAXDOP) of requests made in the workload group, where the default value is 0 and the allowed values ​​are between 0 and 64. This setting overrides the parameter sp_configure 'max degree of parallelism 'and also the query hint OPTION (MAXDOP N).

The GROUP_MAX_REQUESTS parameter defines the maximum amount of concurrent requests executed by the workload group. The default value is 0, ie without limitation. When this number is reached, new connections will wait until the number of simultaneous requests is below the maximum.

Creation Example

Reference: English - Portuguese

The resource pool represents the physical resources of the server. You can think of the pool as a virtual instance of SQL Server within an instance of SQL Server. Two resource pools (internal and default) are created when SQL Server is installed and you can create more pools as needed (maximum 64 user pools).

The internal pool is used for SQL Server background processes and is a priority over any other pool. If it needs all the resources available on the instance, it will use it. In addition to the internal pool, there is also the default pool, which is used for the rest of the connections that have not been classified to use the internal pool nor the user's custom pools.

When you are creating a pool, you must specify the maximum and minimum CPU, Memory, and I / O (IOPS) limits.

The MIN_CPU_PERCENT parameter allows you to specify the minimum CPU value used by all pool requests, and is especially useful when CPU contention occurs as SQL Server will ensure that this value is available for use by connections in this pool. If the pool is not in use, this reserved CPU will be available for use by other pools until the pool in question has connections again.

The MAX_CPU_PERCENT parameter allows you to specify the maximum CPU% that pool requests can use on the instance. Note that the maximum CPU percentage is an opportunistic maximum. If CPU capacity is available, the workload will use it up to 100%. The maximum value will be applied only when there is contention for CPU resources.

The CAP_CPU_PERCENT parameter defines a “real” maximum limit on CPU usage by the pool. Workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT when it is available, but not above the value of CAP_CPU_PERCENT.

The MIN_MEMORY_PERCENT parameter allows you to specify and reserve a minimum amount of memory that will always be pre-allocated in the instance to the pool, regardless of whether the pool is being used or not.

Remember that the sum of the minimum value for all pools cannot be greater than 100% and the value of the pool MAX_CPU_PERCENT parameter must be greater than the value of the MIN_CPU_PERCENT parameter.

The MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME parameters are used to determine the minimum and maximum IOPS amounts used by pool connections.

Creation Example

Reference: English - Portuguese

To check Resource Governor status, simply query the sys.resource_governor_configuration view and parse the column is_enabled.

Here's the list of Resource Governor views:

• sys.resource_governor_configuration
• sys.resource_governor_external_resource_pool_affinity
• sys.resource_governor_external_resource_pools
• sys.resource_governor_resource_pool_affinity
• sys.resource_governor_resource_pools
• sys.resource_governor_workload_groups

Here is the list of Resource Governor DMV's

• sys.dm_resource_governor_configuration
• sys.dm_resource_governor_external_resource_pool_affinity
• sys.dm_resource_governor_external_resource_pools
• sys.dm_resource_governor_resource_pool_affinity
• sys.dm_resource_governor_resource_pool_volumes
• sys.dm_resource_governor_resource_pools
• sys.dm_resource_governor_workload_groups

Now that we've seen how Resource Governor works in theory, let's put it into practice in the example below:

Result:

If you prefer, you can also use the Management Studio interface (GUI) to configure Resource Group and Workload group:

And with that, open the Resource Group and Workload group edit / create interface (it's the same interface for both):

Note that not all parameters are available in the SQL Server Management Studio interface (I tested on the 17.4 version), such as the I / O parameters. Therefore, I suggest that you use the command line to manage Resource Governor.

For those who have never used or tested Resource Governor, there may be the following questions:
- And performance of consultations? Will it change after implementing this feature?
- Does Resource Governor really work?
- Is it even possible to limit resources, such as disk, of certain sessions?

Let's do a simple test to prove it:

• Create a counter in Windows Perfmon (Performance Monitor) to monitor the Disk Read IO / sec counter of the MSSQL $ SQL2016: Resource Pool Stats object.
• Create a Resource Pool by limiting the maximum amount of IOPS (MAX_IOPS_PER_VOLUME) to 200.
• Run DBCC CHECKDB (dirceuresende) WITH NO_INFOMSGS Command
• Wait a few seconds and change the resource pool, increasing the maximum amount of IOPS by 2.000.
• Rerun DBCC CHECKDB (dirceuresende) WITH NO_INFOMSGS Command
• Collect Results and Compare Charts

After performing these steps, let's look at the result:

As seen in the chart, Resource Governor fulfilled its role and correctly limited the amount of disk IOPS used in 200 IOPS. After increasing the limit to 2.000 IOPS, we could see that the disk was able to reach up to 1.000 IOPS and could not only exceed this value due to hardware limitations, where it can also be observed that due to the higher limit of consumption of IOPS, the time to run checkdb was smaller.

To disable Resource Governor on your instance, simply use the following commands:

Remembering that current connections must be terminated (or restart the instance), otherwise Resource Governor will remain active and you will receive this error message:

To also exclude Workloads groups and Resource Pools, you can also use the following commands:

After enabling Resource Governor, you can monitor resource pool CPU usage at a glance using the query below:

Result:

Or monitor all CPU parameters of pools and workload groups:

You can also monitor resource pool I / O usage using the query below:

Result:

You can also use the query below to check which workload group is currently used by active sessions:

Result:

If you want an even more complete version of this query, which can practically replicate sp_whoisactive's behavior, read the post. SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB).

Below, I will list some best practices for using Resource Governor to avoid future problems using this resource:

• Use the DAC: Because all new connections go through the Resource Governor classification function, a problem with this function can make it difficult to access the instance to correct the problem. To get around this easily, you can enable and use Dedicated Administrator Connection (DAC) on your instance, and thus be able to log in and change the sorting function. If you do not know how to do this, read the post Enabling and using dedicated remote administrator connection (DAC) in SQL Server.
• Beware of the classification function: As noted above, a coding error in the sort function can lead to very bad effects on your instance. To do this, avoid using functions or features that can make the response time of this function high. If the function needs to query a physical table, ensure that access is fast enough and the indexes are meeting the conditions of that query.
• Default Pool and Default Workload Group: A good practice when using Resource Governor is to leave some of the resources available to the default pool and default workload group. This is important because all non-SQL Server internal process sessions that cannot be classified into any custom pool will be part of the default pool. If this pool does not have the required resources available, these requests may have slow issues.
• Always monitor: After deploying Resource Governor, you should always be aware of and keep track of request flow and validate whether they are being directed to the right workload groups, and monitor potential new needs that require a change in the sorting function.
• Beware of memory settings: As already mentioned in this post, memory settings are pre-allocated when creating the pool, that is, even if the pool has no active connection and is not using this pre-allocated memory, it will still be reserved by the pool.