SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 3) - Using the Resource Governor

1/29/2011 6:30:45 PM

Using the Resource Governor

The Resource Governor is a new feature that was introduced with SQL Server 2008. It is available only in the Enterprise, Developer, and Evaluation editions of SQL Server. Although the Resource Governor does have a name similar to the Query Governor and can limit execution of expensive queries like the Query Governor, it is a more robust resource management mechanism than the Query Governor. The goal of the Resource Governor is to be more specific in what code it limits on the server and what code is allowed to run unrestricted. We can identify the code to the server through criteria such as which user is running the query, what program the user is using, and what server or database roles the user is a member of. Based on the criteria, we can limit the code as it executes on the server by controlling things such as how many current requests there can be and how much of the available CPU and memory resources they can consume. To accomplish this goal, we will create Resource Pools, Workload Groups, and a Classifier Function.

The terms Classifier Function, Workload Group, and Resource Pool, may sound a little daunting at first, but they are easy to understand once you have gone through the process. We’ll talk first about what each of these terms means and then walk through an example configuration. We’ll start with Resource Pool, but first, here is a quick generalization of each term to help get you thinking:

  • Classifier Function A user-defined function written by us. It is run every time a session starts as part of the login process, and it uses logic based on who the user is, what program the user is running, and so on, to assign the session to a Workload Group, or any other logic we feel is necessary to classify the session’s workload requirements.

  • Workload Group Defines a “policy” for the session and its code. The policy first assigns the “Resource Pool” (see the next bullet item) from which the session will be allocated CPU and memory resources. It then defines a suballocation of the memory from the associated Resource Pool for the particular Workload Group, and sets maximum CPU time thresholds, memory resource time-outs, and maximum degree of parallelism policies.

  • Resource Pool Controls the availability of CPU and Memory resources to the Workload Groups assigned to use that particular Resource Group.

Resource Pools allow us to allocate a portion of the server’s total CPU “bandwidth” and Memory to the statements submitted through a client connection. SQL server has two primary resources that are directly consumable by client code: CPU and Memory. Some applications, and therefore the statements submitted by those applications to the server, are “mission critical.” Other applications may be important, but not critical, while still more applications may be unimportant or trivial. The problem is that sometimes it’s the programs we care less about that can consume an unacceptable level of resources on the server. If that happens, they can prevent the more critical applications from having the resources they need.

Resource Pools allow us to define the minimum and maximum percentages of both CPU and memory utilization. To reflect the resources required by these three different kinds of workloads suggested above (mission critical, important, and trivial), we could create three Resource Pools. The first or, Mission Critical Resource Pool, would have a large percentage of CPU and Memory resources available. We could then create the Important Resource Pool and assign moderate percentages of CPU and Memory resources to it. Third, we could create a Trivial Resource Pool that would have a very limited percentage of CPU and Memory resources. It is important to note that the Resource Pool is a way to configure the available resources, not what code uses those resources. Client connections first get assigned to a Workload Group by the Classifier Function. The Workload Group the client session gets assigned to determines which Resource Pool the client’s session will consume resources from. When multiple Resource Pools are defined, their Minimum CPU and Memory resource allocations cannot total more than 100%. The Maximum percentages are the lesser of the requested maximum and the requested maximum minus the minimum resources of the other pools.

Workload Groups allow us to enforce a policy of execution on the sessions that are assigned to it. That policy includes which Resource Pool the session will allocate resources from. Multiple Workload Groups can be assigned to the same Resource Pools and therefore “compete” for resources in that pool. For that reason, a Workload Group can be assigned an “importance” to help the Resource Governor to resolve arguments concerning which Workload Group should get a resource first. Workload Group policies determine how much of its associated Resource Pool’s memory its sessions can use, how long a session can wait for memory if it is not currently available, how many concurrent requests can be open for all sessions in the Workload Group, and some others. The Workload Group can set a maximum amount of CPU time (in seconds) that a session can use, but SQL Server does not terminate code if it exceeds that time; it only generates an event that we can capture using SQL Trace and report on.

To continue our example, we could create three Workload Groups, and we could call them Mission Critical Group, Important Group, and Trivial Group. In the configuration of those Workload Groups, we could assign them to allocate resources from the Mission Critical Resource Pool, Important Resource Pool, and Trivial Resource Pool, respectively. We determine what Workload Group a client session gets assigned to by creating a CLASSIFIER function.

The CLASSIFIER function is a user-defined function that can use whatever logic we need to determine the name of the Workload Group we would like to assign the session to. As a client logs into his or her workstation and starts an application that connects to SQL Server, a session is started on the server on behalf of the user. As that session starts, during the login process, SQL Server will call the CLASSIFIER function we create. The CLASSIFIER function will use our logic to determine and then return the name of the Workload Group the server should assign the session to. The logic can be based on system functions such as SUSER_SNAME(), APP_NAME(), HOST_NAME(), IS_SRVROLEMEMBER, IS_MEMBER(), CONNECTIONPROPERTY, GETDATE(), as well as any other code we need to determine what kind of workload the session requires, and then direct it to the appropriate Workload Group. (If you don’t know what some or all of the functions just named are, look them up in the SQL Server documentation. While you are at it, look for other useful system functions you could use to help classify your client’s sessions.)

Hopefully, it is starting to make sense. Before we get started, there are a couple more things to mention:

  • Resource Governor is built into SQL Server but is not enabled by default. We use the ALTER RESOURCE GOVERNOR RECONFIGURE statement to enable it.

  • Resource Governor has two Resource Pools and Workload Groups in its initial configuration named “internal” and default.” The default Workload Group is assigned to the default Resource Pool, and the “internal” Workload Group is assigned to the internal Resource Pool.

  • The internal Resource Pool and Workload Group are reserved for internal SQL server processes and cannot be managed by us.

  • The default Workload Group is the group to which sessions are assigned whenever there is no other Workload Group appropriate for the session, or the Workload Group name the CLASSIFIER function returns cannot be found.

  • SQL Server provides a number of views that we can use to investigate and monitor the Resource Governor configuration. They include but are not limited to: sys.resource_governor_configuration, sys.resource_governor_resource_pools, sys.resource_governor_workload_groups, sys.dm_resource_governor_workload_groups, sys.dm_resource_governor_resource_pools, sys.dm_resource_governor_configuration

  • Connections made via the Dedicated Administrator Connection (DAC) are not subject to the Classifier and do not run with limited resources. This can be used when the Resource Governor has been configured incorrectly or needs to be diagnosed.

To wrap it up, let’s look at an example. In this example we will:

  1. Create a Resource Pool that guarantees a minimum amount of CPU and Memory resources for people using the Accounting App.

  2. Create a Workload Group that sets a policy for the Accounting application and assigns it to the Accounting Resource Pool made in step 1.

  3. Create a CLASSIFIER function that uses the APP_NAME() function to determine if the session being created is for our accounting application, Great Plains.

  4. Assign our CLASSIFIER function to be used by the Resource Governor.

  5. Enable the Resource Governor (so it will begin to use the new configuration).

  6. After connecting with Great Plains, query the server to find out what group it was assigned to.

USE master;
-- Step 1: Create the "Accounting" Resource Pool with:

-- 25% CPU Guaranteed (Minimum)
-- 50% Memory Guaranteed
-- Step 2: Create the "Accounting" Workload Group with:
-- High Importance
-- 30 Seconds of CPU Time threshold
-- 100 Percent of the Memory in the Accounting Resource Pool
-- 30 Seconds timeout if waiting for memory
-- Using the Accounting Resource Pool
CREATE WORKLOAD GROUP [Accounting] WITH(group_max_requests=0,
USING [Accounting];
-- Step 3: Create the Classifier Function return the Workload Group name:
-- Make sure to make the Classifier Function in the Master database
-- "Accounting" - If the APP_NAME() is like "Great Plains"
-- "default" - for everything else
CREATE FUNCTION dbo.ufnClassifier()
RETURNS sysname
DECLARE @Group sysname
IF APP_NAME() LIKE '%Great Plains%'
SET @Group = 'Accounting'
SET @Group = 'default'

-- Step 4: Assign our Classifier Function to be used by the Resource Governor
-- Step 5: Enable the Resource Governor
-- Step 6: Query the system dynamic management views to see
-- which Workload Group and thus resource Pool
-- a live session from Great Plains was assigned to...
SELECT s.session_id, s.program_name, AS workload_group, as pool_name
FROM sys.dm_exec_sessions AS s
JOIN sys.resource_governor_workload_groups as w
JOIN sys.resource_governor_resource_pools as p
on w.pool_id = p.pool_id
ON s.group_id = w.group_id
WHERE program_name='Great Plains';
session_id program_name workload_group pool_name
---------- ------------ -------------- ----------
61 Great Plains Accounting Accounting


The Resource Governor is available only in the Enterprise, Evaluation, and Developer editions. Test questions often like to trick you by playing with the editions available in the criteria, so pay attention!

  •  Reporting Services with SQL Azure : Deploying the Report & Creating a Subreport
  •  Reporting Services with SQL Azure : Creating the Report Design
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 2) - Using RANK, DENSE_RANK and NTILE
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 1) - Using ROW_NUMBER
  •  SQL Server 2008 : Demystifying Data Types - Computed Columns
  •  Programming Microsoft SQL Server 2005: Overview of SQL CLR - Visual Studio/SQL Server Integration
  •  Programming Microsoft SQL Server 2005: DDL Triggers and Notifications
  •  Programming Microsoft SQL Server 2005: Enabling CLR Integration
  •  Reporting Services with SQL Azure : Creating the SQL Azure Data Source
  •  Reporting Services with SQL Azure : Starting a SQL Azure–Based Report
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone