We have seen Resource Governor’s three main components separately. Now let’s look at how they work together. We will use the same resource pools (POOLA and POOLB), workload groups (UserA, ReportApp), and classifier function (Class_funct) that we created earlier to examine how Resource Governor handles the flow of requests.

If you look carefully at the classifier function we created, you will see that it checks the name of the application that the request is coming from and handles two cases: ‘%MANAGEMENT STUDIO%’ and ‘%REPORT%’. All other requests are “thrown on the floor”-the classifier function does not handle them. Therefore, when a request from SQL Server Agent or the website (IIS) comes through the classifier function, it does not return a group name because it does not handle those requests. Because any unhandled request from the classifier function goes to the default group, which in turn sends it to the default pool, those requests will end up in the default pool.

Let’s look at the other two requests in Figure 1. One is from a report server, and the other is from SQL Server Management Studio (SSMS). In the classifier function, we defined that if the application name is SSMS, the return value is UserA. That means that the request will be sent to the workload group UserA, which was created by specifying a resource pool of POOLA. So the request from SSMS will go to the POOLA pool. When the application name is Report Server, the classifier function returns ReportApp as the workload group. The ReportApp workload group was created with the USING POOLB clause, so the request from the report server will be directed to the POOLB resource pool.

Some Resource Governor Misconceptions

In some cases, Resource Governor will act differently than how it is set up. Let’s look at a few of these cases.

1. No resource contention. Although you have set up some limitation to the resources for a particular pool, you might find cases when the limits are not taken into account. Consider a scenario where you have set up a pool with a CPU cap of 25%. If at the time of executing a task in that pool there are no other active or workable SPIDS in other pools, this cap will not be taken into account.
2. Importance. Let’s say you have two tasks: Task A with importance of HIGH, and Task B with importance of LOW. These settings do not mean that Task A will execute first, and then Task B will be executed. Importance is nothing but a numeric weight given to a request. What the importance settings actually do is to tell the scheduler to give 3 out of 4 units of work to Task A and the other one part to Task B. Both tasks can execute simultaneously.
3. Waiting or pre-emptive state. If a query is waiting for other resources such as I/O, another query that has a cap set might use all available resources even though the first task is still active. That is because Resource Governor does not recognize a task that is in a WAITING for resource mode.
4. Off–limit requests. There are quite a few requests that are off limits to Resource Governor. Extended stored procedures, for example, are usually off limits, including Database Mail, linked server queries, and COM/OLE automation.

By default, there are two resource pools: internal and default. You cannot alter the internal pool in any way because it is reserved for SQL Server’s internal processes. But you can alter the default pool. You can also create additional user-defined resource pools, up to a maximum of 18. The permission required to create a resource pool is CONTROL SERVER.

For each pool, you can configure the MIN and MAX for CPU and memory. For CPU, the MIN and MAX settings let you control the CPU usage for each pool. For memory, instead of having one memory broker for the whole instance, these settings let you have one memory broker and two resource semaphores for each pool to handle memory management. Resource semaphores are used for synchronization.

The following code shows two examples of how to create user-defined resource pools using T-SQL.

For these changes to take effect, you need to reconfigure Resource Governor, as follows:

You have four options when creating user-defined pools:

The workload group, as its name implies, groups a set of similar tasks or queries and routes them to a specific resource pool. We saw that the resource pool can control CPU and memory resources. The workload group provides you with additional controls that you can leverage to get the most out of Resource Governor. Using workload groups, you can prioritize and adjust the resources available for each set of defined tasks or queries.

As with the resource pools, by default, there are two workload groups: internal and default. You can assign a workload group to only one resource pool. But one resource pool can have multiple workload groups. You cannot assign a workload group to the internal group. The permission required to create or alter a workload group is CONTROL SERVER.

The following code shows you how to create two workload groups using T-SQL:

You need to follow all modifications to Resource Governor components with a reconfigure statement:

In this example, POOLA and POOLB are user-defined resource pools. If you do not specify the USING clause, the workload group will be assigned to the default pool. Let’s look at some other parameters you can use when creating a workload group:

1. IMPORTANCE: This parameter lets you specify the importance of the incoming request with respect to that group. You can provide one of three values: LOW, MEDIUM (the default value), or HIGH. Importance does not mean priority as it does with the windows scheduler. The value of importance has no effect on other pools. Importance is used to position the request in the list of the same scheduler. Also note that if there are enough schedulers available and you have one query with HIGH importance and one with LOW importance, the two queries can run concurrently. The current ratio between the three values of importance is 1:3:9, where 1 is LOW, 3 is MEDIUM, and 9 is HIGH.
2. REQUEST_MAX_CPU_TIME_SEC: This value specifies the maximum amount of CPU time that will be allocated for this query or task request. The value must be greater than or equal to 0. A value is 0 means there is no limit. This parameter works slightly different from the way Query Governor works. When Query Governor looks at a plan and finds that it exceeds the time specified, it will not attempt to run the query. But in a workload group, the query will continue to execute even when it takes more time than specified in this parameter. An alert will be raised the first time it exceeds the time limit, and you can find details in the event class CPU Threshold Exceeded, which returns CPU in milliseconds, Type of Event, GroupID, OwnerID, SPID, and StartTime. Keep in mind that the detection sweep runs every 5 seconds and might miss events that exceed the specified threshold by less than five seconds. Because of this, don’t be discouraged from setting a value less than 5 seconds. You can set the parameter value for 3 seconds and still get an alert when the query or task runs more than 3 seconds.

3. MAX_DOP: This value specifies the maximum degree of parallelism allowed for a parallel request. Values must range between 0 and 64. A couple of things to remember when setting the value for MAX_DOP are:

1. MAX_DOP as a query hint overrides the workload group MAX_DOP setting as long as the query hint value is not above the value of the group.
2. The MAX_DOP value in a workload group overrides the value set by sp_configure at the database level.
3. The MAX_DOP setting will have no effect if the query is marked as serial at compile time.

There are three scenarios when the request or task will be classified to the default group:

1. There was a general classification failure from the classifier function.
2. The classifier function returns a non-existing workload group.
3. The classifier function has no criteria to classify the function.

When and Why Use Resource Governor?

Runaway Queries