Resource Governor provides a method for throttling applications at the SQL Server layer by imposing limits on CPU, memory, and physical I/O on different classifications of connection. This chapter discusses the concepts the Resource Governor uses before demonstrating how to implement them. We then look at how to monitor the effect that Resource Governor has on resource utilization.
Resource Governor Concepts
Resource Governor uses resource pools to define a subset of server resources, workload groups as logical containers for similar session requests, and a classifier function to determine to which workload group a specific request should be assigned. The following sections discuss each of these concepts.
Resource Pool
A resource pool defines a subset of server resources that sessions can utilize. When Resource Governor is enabled, three pools are automatically created: the internal pool, the default pool, and the default external pool. The internal pool represents the server resources the instance uses. This pool cannot be modified. The default pool is designed as a catchall pool and is used to assign resources to any session that is not assigned to a user-defined resource pool. You cannot remove this pool; however, you can modify its settings. The default external pool is used to govern resources used by the rterm.exe, BxlServer.exe, and python.exe processes, which are used by Machine Learning Services. The default external resource pool can be modified, but not removed, and new external resource pools can be added.
Resource Pools’ Simple Effective Maximum Percentages
Resource Pool* | Min CPU % | Max CPU % | Effective Max CPU % | Calculation |
|---|---|---|---|---|
Default | 0 | 100 | 75 | Smallest(75,(100–0-25)) = 75 |
SalesApplication | 25 | 75 | 75 | Smallest(75,(100–0-0)) = 75 |
Default External | 0 | 100 | 75 | Smallest(75,(100–0-25)) = 75 |
Resource Pools’ Effective Maximum Percentages After Implicit Reductions
Resource Pool* | Min CPU % | Max CPU % | Effective Max CPU % | Calculation |
|---|---|---|---|---|
Default | 0 | 100 | 25 | Smallest((100,(100-sum(25,50,0)) = 25 |
SalesApplication | 25 | 75 | 50 | Smallest((75,(100-50-0)) = 50 |
AccountsApplication | 50 | 80 | 75 | Smallest((80,(100-25-0)) = 75 |
Default External | 0 | 100 | 25 | Smallest((100,(100-sum(25,50,0)) = 25 |
Workload Group
A resource pool can contain one or more workload groups. A workload group represents a logical container for similar sessions that have been classified as similar by executing a classifier function, which is covered in the next section. For example, in the SalesApplication resource pool mentioned earlier, we can create two workload groups. We can use one of these workload groups as a container for normal user sessions while using the second as a container for reporting sessions.
This approach allows us to monitor the groups of sessions separately. It also allows us to define separate policies for each set of sessions. For example, we may choose to specify that sessions used for reporting have a lower MAXDOP (maximum degree of parallelism) setting than the sessions used for standard users, or that sessions used for reporting should only be able to specify a limited number of concurrent requests. These settings are in addition to the settings we can configure at the resource pool level.
Classifier Function
A classifier function is a scalar function, created in the Master database. It is used to determine which workload group each session should be assigned to. Every new session is classified using a single classifier function, with the exception of DACs (dedicated administrator connections), which are not subject to Resource Governor. The classifier function can group sessions based on virtually any attribute that it is possible to code within interpreted SQL. For example, you may choose to classify requests based upon username, role membership, application name, host name, login property, connection property, or even time.
Implementing Resource Governor
To configure Resource Governor on an instance, you must create and configure one or more resource pools, each with one or more workload groups. In addition, you must also create a classifier function. Finally, you need to enable Resource Governor, which results in all subsequent sessions being classified. These topics are discussed in the following sections.
Creating Resource Pools
It is possible to create a maximum of 64 resource pools per instance. Let’s create a resource pool through SQL Server Management Studio, drill through Management ➤ Resource Governor in Object Explorer, and then select New Resource Pool from the Resource Pools context menu. This causes the Resource Governor Properties dialog box to be invoked.
In the Resource Pools section of this dialog box, create a new row in the grid and populate it with the information we need to create our new resource pool. In our case, you should add the details for a resource pool named SalesApplication, which has a Minimum CPU % of 25, a Maximum CPU % of 75, a Minimum Memory % of 25, and a Maximum Memory % of 40.
Highlighting a resource pool causes the workload groups associated with that resource pool to display in the Workload Groups for Resource Pool section of the screen. Here, you can add, amend, or remove resource pools at the same time. However, you can also access this dialog box by drilling through Management ➤ Resource Governor ➤ [Resource Pool name] and then selecting New Workload Group from the Workload Groups context menu.
In this scenario, the maximum memory limit is a hard limit. This means that no more than 40% of the memory available to this instance is ever allocated to this resource pool. Also, even if no sessions are using this resource pool, 25% of the memory available to the instance is still allocated to this resource pool and is unavailable to other resource pools.
In contrast, the maximum CPU limit is soft, or opportunistic. This means that if more CPU is available, the resource pool utilizes it. The cap only kicks in when there is contention on the processor.
It is possible to configure a hard cap on CPU usage. This is helpful in PaaS (platform as a service) or DaaS (database as a service) environments where clients are charged based on CPU usage and you need to ensure consistent billing for their applications. A client can easily dispute a bill if they have agreed to pay for 40% of a core, but the soft cap allows them to reach 50%, resulting in a higher charge automatically being applied. Implementing this is discussed later in this section.
CREATE RESOURCE POOL Arguments
Argument | Description |
|---|---|
pool_name | The name that you assign to the resource pool. |
MIN_CPU_PERCENT | Specifies the guaranteed average minimum CPU resource available to the resource pool as a percentage of the CPU bandwidth available to the instance. |
MAX_CPU_PERCENT | Specifies the average maximum CPU resource available to the resource pool as a percentage of the CPU bandwidth available to the instance. This is a soft limit that applies when there is contention for the CPU resource. |
CAP_CPU_PERCENT | Specifies a hard limit on the amount of CPU resource available to the resource pool as a percentage of the CPU bandwidth available to the instance. |
MIN_MEMORY_PERCENT | Specifies the minimum amount of memory that is reserved for the resource pool as a percentage of the memory available to the instance. |
MAX_MEMORY_PERCENT | Specifies the maximum amount of memory that the resource pool can use as a percentage of the memory available to the instance. |
MIN_IOPS_PER_VOLUME | Specifies the number of IOPS per volume that is reserved for the resource pool. Unlike CPU and memory thresholds, IOPS are expressed as an absolute value, as opposed to a percentage. |
MAX_IOPS_PER_VOLUME | Specifies the maximum number of IOPS per volume that the resource pool can use. Like the minimum IOPS threshold, this is expressed as an absolute number, as opposed to a percentage. |
AFFINITY SCHEDULER* | Specifies that the resource pool should be bound to specific SQLOS (SQL operating system) schedulers, which in turn map to specific virtual cores within the server. Cannot be used with AFFINITY NUMANODE. Specify AUTO to allow SQL Server to manage the schedulers that are used by the resource pool. Specify the range of scheduler IDs. For example (0, 1, 32 TO 64). |
AFFINITY NUMANODE* | Specifies that the resource pool should be bound to a specific range of NUMA nodes. For example (1 TO 4). Cannot be used with AFFINITY SCHEDULER. |
When we are working with minimum- and maximum-IOPS-per-volume thresholds, we need to take a few things into account. First, if we do not set a maximum IOPS limit, SQL Server does not govern the IOPS for the resource pool at all. This means that if you configure minimum IOPS limits for other resource pools, they are not respected. Therefore, if you want Resource Governor to govern I/O, always set a maximum IOPS threshold for every resource pool.
It is also worth noting that the majority of I/O that you can control through Resource Governor is read operations. This is because write operations, such as Lazy Writer and Log Flush operations, occur as system operations and fall inside the scope of the internal resource pool. Because you cannot alter the internal resource pool, you cannot govern the majority of write operations. This means that using Resource Governor to limit I/O operations is most appropriate when you have a reporting application or another application with a high ratio of reads to writes.
Finally, you should be aware that Resource Governor can only control the number of IOPS; it cannot control the size of the IOPS. This means that you cannot use Resource Governor to control the amount of bandwidth into a SAN an application is using.
CREATE EXTERNAL RESOURCE POOL Arguments
Argument | Description |
|---|---|
pool_name | The name that you assign to the resource pool. |
MAX_CPU_PERCENT | Specifies the average maximum CPU resource available to the resource pool as a percentage of the CPU bandwidth available to the instance. This is a soft limit that applies when there is contention for the CPU resource. |
AFFINITY SCHEDULER | Specifies that the resource pool should be bound to specific SQLOS (SQL operating system) schedulers, which in turn map to specific virtual cores within the server. Cannot be used with AFFINITY NUMANODE. Specify AUTO to allow SQL Server to manage the schedulers that are used by the resource pool. Specify the range of scheduler IDs. For example (0, 1, 32 TO 64). |
MAX_MEMORY_PERCENT | Specifies the maximum amount of memory that the resource pool can use as a percentage of the memory available to the instance. |
MAX_PROCESSES | Specifies the maximum number of processes allowed within the pool at any given time. The default is 0, which limits the number of processes by server resources only |
Creating a Resource Pool
Creating Workload Groups
The Resource Governor Properties dialog box
You can see that with the SalesApplication resource pool highlighted in the Resource Pools section of the dialog box, we have created two rows within the Workload Groups section of the screen. Each of these rows represents a workload group that is associated with the SalesApplication resource pool.
We have configured the SalesUsers workload group to allow a maximum of 100 simultaneous requests and a MAXDOP of 4, meaning that requests classified under this workload group are able to use a maximum of four schedulers.
We have configured the Managers workload group to allow a maximum of ten simultaneous requests and use a maximum of one scheduler. We have also configured this workload group to be able to use a maximum of 10% of the memory that the resource pool can reserve, as opposed to the default of 25%.
If the Memory Grant % setting is set to 0, then any requests classified under that workload group are blocked from running any operations that require a SORT or HASH JOIN physical operator. If queries need more than the specified amount of RAM, then SQL Server reduces the DOP for that query in an attempt to reduce the memory requirement. If the DOP reaches 1 and there is still not enough memory, then Error 8657 is thrown.
CREATE WORKLOAD GROUP Arguments
Argument | Description |
|---|---|
group_name | Specifies the name of the workload group. |
IMPORTANCE | Can be configured to HIGH, MEDIUM, or LOW and allows you to prioritize requests in one workload group above another. |
REQUEST_MAX_MEMORY_GRANT_PERCENT | Specifies the maximum amount of memory that any one query can use from the resource pool expressed as a percentage of the memory available to the resource pool. |
REQUEST_MAX_CPU_TIME_SEC | Specifies the amount of CPU time, in seconds, that any one query can use. It is important to note that if the threshold is exceeded, then an event is generated that can be captured with Extended Events. The query is not cancelled, however. |
REQUEST_MEMORY_GRANT_TIMEOUT_SEC | Specifies the maximum amount of time that a query can wait for a work buffer memory to become available before it times out. The query only times out under memory contention, however. Otherwise, the query receives the minimum memory grant. This results in performance degradation for the query. The maximum wait time is expressed in seconds. |
MAX_DOP | The maximum number of processors that a single parallel query can use. The MAXDOP for a query can be further restrained by using query hints, by changing the MAXDOP setting for the instance, or when the relational engine chooses a serial plan. |
GROUP_MAX_REQUESTS | Specifies the maximum number of concurrent requests that can be executed within the workload group. If the number of concurrent requests reaches this value, then further queries are placed in a waiting state until the number of concurrent queries falls below the threshold. |
USING | Specifies the resource pool with which the workload group is associated. If not specified, then the group is associated with the default pool. |
Workload group names must be unique, even if they are associated with different pools. This is so they can be returned by the classifier function.
Creating Workload Groups
Creating a Classifier Function
A classifier function is a scalar UDF (user-defined function) that resides in the Master database. It returns a value of type SYSNAME, which is a system-defined type equivalent to NVARCHAR(128). The value returned by the function corresponds to the name of the workload group into which each request should fall. The logic within the function determines which workload group name is returned. You only ever have one classifier function per instance, so you need to modify the function if you add additional workload groups.
- 1.
If the request is made under the context of the SalesUser login, then the request should fall under the SalesUsers workload group.
- 2.
If the request is made by the SalesManager login, then requests should be placed in the Managers workload group.
- 3.
If the request is made by the ReportsUser login and the request was made from a server named ReportsApp, then the request should fall into the InternalReports workload group.
- 4.
If the request is made by the ReportsUser login but did not originate from the ReportsApp server, then it should fall into the ExternalReports workload group.
- 5.
All other requests should be placed into the default workload group.
Before creating our classifier function, we prepare the instance. To do this, we first create the Chapter25 database. We then create the SalesUser, ReportsUser, and SalesManager logins, with Users mapped to the Chapter25 database. (Further detail on security principles can be found in Chapter 10.) Listing 25-3 contains the code we need to prepare the instance.
The users are mapped to the Chapter25 database for the purpose of this example, but you can make the queries against any database in the instance.
Preparing the Instance
System Functions for Implementing Business Rules
Function | Description | Business Rule(s) |
|---|---|---|
SUSER_SNAME() | Returns the name of a login | 1, 2, 3, 4 |
HOST_NAME() | Returns the name of the host from which the request was issued | 3, 4 |
When we create a classifier function, it must follow specific rules. First, the function must be schema-bound. This means that any underlying objects that are referenced by the function cannot be altered without the function first being dropped. The function must also return the SYSNAME data type and have no parameters.
It is worth noting that the requirement for the function to be schema-bound is significant, and it poses limitations on the flexibility of Resource Governor. For example, it would be very useful if you were able to delegate workloads based upon database role membership; however, this is not possible, because schema-bound functions cannot access objects in other databases, either directly or indirectly. Because the classifier function must reside in the Master database, you cannot access information regarding database roles in other databases.
As with all things, there are workarounds for this issue. For example, you can create a table in the Master database that maintains role membership from user databases. You can even keep this table updated automatically by using a combination of views and triggers in the user database. The view would be based on the sys.sysusers catalog view and the trigger would be based on the view that you created. This would be a complex design, however, which would pose operational challenges to maintain.
Creating the Classifier Function
Testing the Classifier Function
Testing Business Rules 1 and 2
The result of running these two queries shows that business rules 1 and 2 are working as expected.
To test business rule 4, we can use the same process we used to validate business rules 1 and 2. The only difference is that we change the execution context to ReportsUser. In order to validate rule 3, we use the same process, but this time, we invoke the query from a server named ReportsApp.
If you do not have access to a server named ReportsApp, then update the function definition to use a server name that you do have access to.
Monitoring Resource Governor
SQL Server exposes dynamic management views (DMVs) that you can use to return statistics relating to resource pools and workload groups. You can also monitor Resource Governor’s usage using Windows’ Performance Monitor tool, however, and this gives you the advantage of a graphical representation. The following sections discuss both of these approaches to monitoring Resource Governor.
Monitoring with Performance Monitor
DBAs can monitor how resource pools and their associated workload groups are being utilized by using Performance Monitor, which is built into Windows. You can access Performance Monitor from Control Panel ➤ Administrative Tools or by searching for Perfmon in the Start menu.
To follow the demonstrations in this section, you should be running a Windows Server operating system.
Two categories are available to Performance Monitor that relate to Resource Governor. The first is MSSQL$[INSTANCE NAME]:Resource Pool Stats. This contains counters that relate to the consumption of resources, which have been made available to resource groups. An instance of each counter is available for each resource group that has been configured on the instance.
Adding Workload Group Stats.
Resource Pool Stats
Viewing CPU utilization
The following scripts are likely to return a lot of data.
Generating Load Against the SalesUsers and Managers Workload Groups
You can see that the CPU usage for the SalesUsers and Managers workload groups is almost identical, which means that the Resource Governor implementation is working as expected.
Monitoring with DMVs
Columns Returned by sys.dm_resource_governor_resource_pools
Column | Description |
|---|---|
pool_id | The unique ID of the resource pool |
name | The name of the resource pool |
statistics_start_time | The date/time of the last time the resource pool’s statistics were reset |
total_cpu_usage_ms | The total CPU time used by the resource pool since the statistics last reset |
cache_memory_kb | The total cache memory currently being used by the resource pool |
compile_memory_kb | The total memory the resource pool is currently using for compilation and optimization |
used_memgrant_kb | The total memory the resource pool is using for memory grants |
total_memgrant_count | A count of memory grants in the resource pool since the statistics were reset |
total_memgrant_timeout_count | A count of memory grant time-outs in the resource pool since the statistics were last reset |
active_memgrant_count | A count of current memory grants within the resource pool |
active_memgrant_kb | The total amount of memory currently being used for memory grants in the resource pool |
memgrant_waiter_count | A count of queries currently pending, waiting for memory grants within the resource pool |
max_memory_kb | The maximum amount of memory the resource pool can reserve |
used_memory_kb | The amount of memory the resource pool currently has reserved |
target_memory_kb | The amount of memory that the resource pool is currently trying to maintain |
out_of_memory_count | A count of failed memory allocations for the resource pool |
min_cpu_percent | The guaranteed average minimum CPU % for the resource pool |
max_cpu_percent | The average maximum CPU % for the resource pool |
min_memory_percent | The guaranteed minimum amount of memory that is available to the resource pool during periods of memory contention |
max_memory_percent | The maximum percentage of server memory that can be allocated to the resource pool |
cap_cpu_percent | The hard limit on the maximum CPU % available to the resource pool |
Columns Returned by sys.dm_resource_governor_workload_groups
Column | Description |
|---|---|
group_id | The unique ID of the workload group. |
name | The name of the workload group. |
pool_id | The unique ID of the resource pool with which the workload group is associated. |
statistics_start_time | The date/time of the last time the workload group’s statistics were reset. |
total_request_count | A count of the number of requests in the workload group since the statistics were last reset. |
total_queued_request_count | The number of requests within the workload group that have been queued as a result of the GROUP_MAX_REQUESTS threshold being reached since the statistics were last reset. |
active_request_count | A count of requests that are currently active within the workload group. |
queued_request_count | The number of requests within the workload group that are currently queued as a result of the GROUP_MAX_REQUESTS threshold being reached. |
total_cpu_limit_violation_count | A count of requests in the workload group that have exceeded the CPU limit since the statistics were last reset. |
total_cpu_usage_ms | The total CPU time used by requests within the workload group since the statistics were last reset. |
max_request_cpu_time_ms | The maximum CPU time used by any request within the workload group since the last time the statistics were reset. |
blocked_task_count | A count of tasks within the workload group that are currently blocked. |
total_lock_wait_count | A count of all lock waits that have occurred for requests within the workload group since the last time the statistics were reset. |
total_lock_wait_time_ms | A sum of time that locks have been held by requests within the workload group since statistics were last reset. |
total_query_optimization_count | A count of all query optimizations that have occurred within the workload group since the statistics were reset. |
total_suboptimal_plan_generation_count | A count of all suboptimal plans that have been generated within the workload group, since the last time the statistics were reset. These suboptimal plans indicate that the workload group was experiencing memory pressure. |
total_reduced_memgrant_count | A count of all memory grants that have reached the maximum size limit within the workload group since the last time the statistics were reset. |
max_request_grant_memory_kb | The size of the largest single memory grant that has occurred within the workload group since the last time the statistics were reset. |
active_parallel_thread_count | A count of how many parallel threads are currently in use within the workload group. |
importance | The current value specified for the workload group’s importance setting. |
request_max_memory_grant_percent | The current value specified for the workload group’s maximum memory grant percentage. |
request_max_cpu_time_sec | The current value specified for the workload group’s CPU limit. |
request_memory_grant_timeout_sec | The current value specified for the workload group’s memory grant time-out. |
group_max_requests | The current value specified for the workload group’s maximum concurrent requests. |
max_dop | The current value specified for the workload group’s MAXDOP. |
Reporting on CPU Usage
Resetting Resource Governor Statistics
Columns Returned by dm_resource_governor_resource_pool_affinity
Column | Description |
|---|---|
pool_id | The unique ID of the resource pool. |
processor_group | The ID of the logical processor group. |
scheduler_mask | The binary mask, which represents the schedulers that are affinitized with the resource pool. For further details on interpreting this binary mask, please refer to Chapter 5. |
Scheduling a Binary Mask for Each Resource Pool
Columns Returned by dm_resource_governor_resource_pool_volumes
Column | Description |
|---|---|
pool_id | The unique ID of the resource pool |
volume_name | The name of the disk volume |
min_iops_per_volume | The current configuration for the minimum number of IOPS per volume for the resource pool |
max_iops_per_volume | The current configuration for the maximum number of IOPS per volume for the resource pool |
read_ios_queued_total | The total read I/Os queued for the resource pool against this volume since the last time the statistics were reset |
read_ios_issued_total | The total read I/Os issued for the resource pool against this volume since the last time the statistics were reset |
read_ios_completed_total | The total read I/Os completed for the resource pool against this volume since the last time the statistics were reset |
read_bytes_total | The total bytes read for the resource pool against this volume since the last time the statistics were reset |
read_io_stall_total_ms | The cumulative time between read I/O operations being issued and completed for the resource pool against this volume since the last time the statistics were reset |
read_io_stall_queued_ms | The cumulative time between read I/O operations arriving and being completed for the resource pool against this volume since the last time the statistics were reset |
write_ios_queued_total | The total write I/Os queued for the resource pool against this volume since the last time the statistics were reset |
write_ios_issued_total | The total write I/Os issued for the resource pool against this volume since the last time the statistics were reset |
write_ios_completed_total | The total write I/Os completed for the resource pool against this volume since the last time the statistics were reset |
write_bytes_total | The total bytes written for the resource pool against this volume since the last time the statistics were reset |
write_io_stall_total_ms | The cumulative time between write I/O operations being issued and completed for the resource pool against this volume since the last time the statistics were reset |
write_io_stall_queued_ms | The cumulative time between write I/O operations arriving and being completed for the resource pool against this volume since the last time the statistics were reset |
io_issue_violations_total | The total number of times that more I/O operations were performed against the resource pool and volume than are allowed by the configuration |
io_issue_delay_total_ms | The total time between when I/O operations were scheduled to be issued and when they were actually issued |
You can use the sys.dm_resource_governor_resource_pool_volumes DMV to determine if your resource pool configuration is causing latency by adding the read_io_stall_queued_ms and write_io_stall_queued_ms and then subtracting this value from the total of read_io_stall_total_ms added to write_io_stall_total_ms, as shown in Listing 25-10. This script first alters the default resource pool so that IOPS are governed before subsequently reporting on I/O stalls.
Remember that you are likely to see far fewer write operations than read operations in user-defined resource pools. This is because the vast majority of write operations are system operations, and therefore, they take place within the internal resource pool.
Discovering If Resource Pool Configuration Is Causing Disk Latency
If you do not see any I/O stalls, create a database on a low-performance drive and run some intensive queries against it before you rerun the query in Listing 25-10.
Summary
Resource Governor allows you to throttle applications at the SQL Server instance level. You can use it to limit a request’s memory, CPU, and disk usage. You can also use it to affinitize a category of requests with specific scheduler or NUMA ranges, or to reduce the MAXDOP for a category of requests.
A resource pool represents a set of server resources and a workload group is a logical container for similar requests that have been classified in the same way. Resource Governor provides an internal resource pool and workload group for system requests and a default resource pool and workload group as a catchall for any requests that have not been classified. Although the internal resource pool cannot be modified, user-defined resource pools have a one-to-many relationship with workload groups.
Requests made to SQL Server are classified using a user-defined function, which the DBA must create. This function must be a scalar function that returns the sysname data type. It must also be schema-bound and reside in the Master database. DBAs can use system functions, such as USER_SNAME(), IS_MEMBER(), and HOST_NAME(), to assist them with the classification.
SQL Server provides four dynamic management views (DMVs) that DBAs can use to help monitor Resource Governor configuration and usage. DBAs can also monitor Resource Governor usage using Performance Monitor, however, and this gives them the advantage of a visual representation of the data. When taking this approach, you will find that Performance Monitor exposes counter categories for resource pools and workload groups for each instance that resides on the server. The counters within these categories have one instance for each resource pool or workload group, respectively, that is currently configured on the instance.