Chapter 16 Managing Workloads and Consumption with Resource Governor
Resource Governor is a new feature and one of the most anticipated management features in SQL Server 2008. It bolsters SQL Server performance by allowing DBAs to establish resource limits and priorities on processor and memory-related workloads and resource pools. By defining resource limits on resource consumption, it is possible to prioritize multiple workloads in order to ensure consistent performance and maximize the experience of the end users and the efficiency of the applications without degradation. It is important to realize from the start that Resource Governor will only throttle processes if SQL Server is experiencing CPU or memory pressure, and it will only throttle incoming requests. For example, a process that consumes high CPU will not be throttled, but the next process may be throttled.
Resource Governor is unable to throttle high I/O consumers (processes that read and write excessively from disk) in the SQL Server 2008 release, but this is planned for a future release.
Resource Governor is exciting for DBAs as it will allow them to control
Runaway processes— Processes that could otherwise degrade overall SQL Server performance; for example, a badly written cursor would no longer be able to consume all CPU, or a query that does not have appropriate indexes could not cause a CPU spike, or a long-running query that returns a large rowset could not consume large amounts of memory in the process.
Login priorities— The DBA can selectively give high priorities to groups of logins, and lower priorities to other logins; for example, reporting users could have a lower priority than logins associated with order entry applications.
Unpredictable responses— By throttling high-resource consumers, SQL Server will be able to deliver predictable response times, as the high-resource consumers will not degrade SQL Server performance the way they would in an ungoverned SQL Server infrastructure.
Note
Resource Governor only applies to the SQL Server Database Engine; it does not apply to Reporting Services, Analysis Services, or Integration Servers. Also, it is not instance aware; in other words, another instance running on the same SQL Server can still degrade performance on all instances.
Before we start using Resource Governor, it is essential to have a good grasp of its concepts. Resource Governor has three components. In this section we will look at these components which will help us to understand them and implement Resource Governor in your enterprise.
Workload— A workload is a process that can be identified by Resource Governor and throttled by it. It can consist of one or more processes. Resource Governor cannot throttle active processes; it can only throttle processes when they log in.
Classifier function— A classifier function is a TSQL function that is invoked at login and will determine which workload group the process will execute in. Resource Governor can only use a single classifier function at one time, but you can dynamically change the classifier function or change the classifier function that Resource Governor uses. Workloads will be classified by the classifier function after the login is authenticated and logon triggers are executed.
Resource pool— Resource Governor parcels out CPU and RAM into groups called pools. Think of a resource pool as a SQL Server instance. A SQL Server shares resources among other instances running on a single physical machine. Similarly, Resource Governor shares resources among resource pools.
By default two resource pools are created for Resource Governor, a default and an internal resource pool. All internal database engine processes will run in the internal resource pool. You cannot modify the properties of the internal resource pool, nor can you create workload groups to run inside it. The default resource pool is the pool that all workloads will run in, unless you define another resource pool, and the classifier function classifies workloads to run in workload groups that exist in other resource pools. You can create your own user resource pools and modify their properties on the fly. Changes to user resource pools will apply to newly logged-on workloads and not to current workloads.
A resource pool will have a minimum set of resources assigned to it; these resources are nonoverlapping. Other pools will not be able to use these resources, and the minimum settings for all pools combined cannot exceed 100 percent. A resource pool will also have a maximum set of resources, which are shared with other pools. Although a maximum resource setting for a particular pool can be 100 percent, the effective maximum will likely be less.
Consider the range of pool values in Table 16.1.
Resource Pool A will have a minimum value of 20 for CPU for example, and an effective max of 20 (the maximum for Pool A subtract the minimum for Pool B). We subtract the minimum of Pool B as it and the minimum for the default pool as these are dedicated to these pools and are not overlapping with the other pools. This means that it will share 20 percent of its resources with the other pools if it is not using them. Workload groups are placed in resource pools, and can be dynamically moved from one resource pool to another.
Note that if there is an error in your classifier function, or if the classifier function is unable to classify a workload, it will run in the default resource pool.
Workload Group— A workload group is a container in which one or more workloads will run. A workload group has properties associated with it, like Max CPU, degree of parallelism, and so on, and you can monitor the resource consumption of workload groups inside a resource pool. This is valuable as it will allow you to move a workload from one workload group to another, or a newly created workload group. As with resource pools, by default there are two workload groups, the internal workload group, and the default workload group. You cannot modify the internal workload group, but you can monitor it. The default group will handle all unclassified workloads.
As Resource Governor is a new feature in SQL Server 2008, most DBAs will want to understand where it will apply within their SQL Server infrastructure. Resource Governor will allow for predictable performance by allowing DBAs to “bucket” their workloads into resource pools, which will have preset limits on the amount of CPU and RAM they can consume. Consider a case where the DBA is using backup compression, which is a high-CPU consumer. While the backups are occurring, they will consume CPU that would otherwise be available to other workloads using the SQL Server. The net result is that all workloads using SQL Server will have degraded performance while the backup process is running. If the DBA were to implement a resource pool with a max CPU usage of 25 percent, the backup process would be throttled and the CPU hit while the backup is running would be considerably less than if you weren’t using Resource Governor. The end result is that the backup process would take longer, but the other workloads running while the backup is in operation would continue to offer the same performance levels as before the backup started.
Also consider a case where a cursor or WHILE loop is missing a FETCH NEXT, or increment step. In this case the cursor or WHILE loop would execute the same portion of code and never exit the cursor or WHILE loop. Such a process, called a runaway process, would pre-empt other processes and consume all the CPU. Resource Governor would limit the impact of such runaway processes by limiting their priority and the resources these runaway processes could take.
Resource Governor can also be used to classify workloads and place them in workload groups, thereby isolating these workloads from other processes. For example, by placing a reporting workload in its own workload group, you can set limits on its workload group and minimize the impact of other workloads in other work load groups from adversely affecting the reporting workload. By doing this you can ensure predictable performance for the reporting workload. Another example, a high CPU process could manage so that other workloads would be able to get the CPU resource they require.
By isolating workloads into their own groups it is possible to monitor them and tune the resources that they consume. Resource Governor will allow you to closely monitor CPU and memory consumption of workloads in workload groups so you can correctly allocate resource to this workload to improve the overall performance of all workloads on your SQL Server.
While monitoring workloads with Resource Governor, you can selectively prioritize workloads as Low, Medium, or High, and then create workload groups for prioritized workloads. This will ensure that high priority processes complete faster than lower priority processes. Prior to Resource Governor it was impossible to monitor or perform such prioritizing of your workloads.
As you will see later in this chapter, the best way to deploy Resource Governor is to place all workloads in the default resource pool, observe their resource requirements, and then create resource pools that are appropriate for their resource demands, and resource pools that will limit the impact of these workloads on the entire SQL Server. After these resource pools have been created, these workloads can be moved into these resource pools and SQL Server will dynamically throttle new workloads as they exceed their resource limits.
To control Resource Governor, you must be in the sys_admin role. Resource Governor is disabled by default. To enable Resource Governor, take the following steps:
1. In SQL Server 2008 Management Studio, connect to a SQL Server instance.
2. Expand the Management folder.
3. Right-click on Resource Governor and select Enable.
To enable Resource Governor using TSQL, issue the following command:
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Similarly, you can disable Resource Governor by using the following steps in Management Studio:
1. In SQL Server 2008 Management Studio, connect to a SQL Server instance.
2. Expand the Management folder.
3. Right-click on Resource Governor and select Disable.
Disabling Resource Governor will not drop classifier functions, workload groups, or resource pools. Essentially, disabling Resource Governor will place all workloads in the default resource pool and workload group. The next time you enable Resource Governor, it will govern newly logged-on workloads.
Use the following TSQL command to disable Resource Governor:
ALTER RESOURCE GOVERNOR DISABLE
RECONFIGURE
GO
Issuing repeat calls to disable or enable Resource Governor will not raise an error message.
If you disable Resource Governor, existing workloads will continue to be throttled based on their current resource pool/workload group settings. Only workloads that are connecting on will be assigned to the default resource pool and workload group.
A classifier function is a TSQL function, created in the master database, which evaluates the properties of a workload at login and places the workload in a workload group. The classifier function works by detecting properties of the login and then returning the name of the workload group into which the workload is to be placed to Resource Governor.
The available properties that the classifier function can use are
HOST_NAME()
APP_NAME()
SUSER_NAME()
SUSER_SNAME()
IS_SRVROLEMEMBER()
IS_MEMBER()
LOGINPROPERTY(suser_name(),'DefaultDatabase')
LOGINPROPERTY('MyAccount,' DefaultLanguage')
However, you are free to use any function, or even a lookup table, to classify your workloads.
Be careful when writing your classification function, as a poorly written classification function will be applied to all login sessions and can cause performance problems. The Dedicated Admin Connection (DAC) will not be classified and can be used to troubleshoot problems with the classification function.
Here is an example of a classifier function:
CREATE FUNCTION [dbo].[MyClassifier] ()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS sysname
IF (SUSER_NAME() = 'Backup')
BEGIN
SET @grp_name = 'BackupGroup'
END
ELSE
BEGIN
IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
OR (APP_NAME() LIKE '%QUERY ANALYZER%')
BEGIN
SET @grp_name = 'DevGroup'
END
ELSE
IF (APP_NAME() LIKE '%REPORT SERVER%')
BEGIN
SET @grp_name = 'ReportingGroup'
END
ELSE
SET @grp_name = 'WorkLoadGroup1'
END
RETURN @grp_name
END
GO
This function starts by determining whether the login is the Backup login. If it is, it returns the name BackupGroup, and Resource Governor places this login in the BackupGroup workload group.
It then checks to see if the application name is Management Studio, or Query Analyzer. If so, the classifier function returns the name DevGroup, and Resource Governor will place this login in the DevGroup workload group.
Finally, the classifier function checks to see if the application name is Report Server. If so, it returns the name ReportingGroup to Resource Governor and the workload will be placed in the ReportingGroup workload group. All other workloads will be placed in the workload group WorkLoadGroup1.
After you have written your classification function, you need to configure Resource Governor to use it. Here is an example of how to do this:
ALTER RESOURCE GOVERNOR WITH
(CLASSIFIER_FUNCTION = dbo.MyClassifier);
GO
Note
You will need Control Server permission to make any changes to Resource Governor configuration, including changing the classifier function, which Resource Governor uses.
There are some considerations when writing the classifier function. The classifier function must return the workgroup name using the data type sysname or nvarchar(128). Group names returned are case-sensitive comparisons. For example, Default (referring to the default workload group) might not resolve to the default workload group. The default resource pool and workload group are lowercased.
If your classifier function causes performance problems on your SQL Server, you have two options for logging on to your server and bypassing the classifier function to troubleshoot it:
1. Start SQL Server with the -m switch from the console. Here is an example of how to do this:
At a command prompt, navigate to the location of your SQL Server binaries. They are likely to be at C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinn. Then issue the following command:
sqlservr -con -m
This will put SQL Server into single-user mode. Using the
DAC (explained below) is preferrable.
2. Alternatively, use the DAC (Dedicated Admin Connection). Log on to SQL Server using the DAC. The DAC will not be subject to the classifier function. The DAC is disabled by default on SQL Server, and to enable the DAC, you will need to issue the following command in a Database Engine query window:
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'remote admin connections',1
reconfigure with override
GO
After you have done this you can use the DAC. There are two ways to log on using the DAC:
1. In SQLCMD, log on using the -A parameter. Here is an example:
sqlcmd -S TOR-SQL01Instance01 -U sa -P sapassword -A
Where TOR-SQL01Instance01 is the name of your SQL Server, sa is the sa account, and sapassword is the sa password.
2. To log on to SQL Server using the DAC in SQL Server Management Studio, take the following steps:
a. In SQL Server 2008 Management Studio, connect to a SQL Server instance with no other DACs open, and on the toolbar, click Database Engine Query.
b. In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named TOR-SQL01Instance01, type ADMIN: TOR-SQL01Instance01.
c. Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.
To create a resource pool, take the following steps:
1. In SQL Server 2008 Management Studio, connect to a SQL Server Instance.
2. Expand the Management folder.
3. Right-click on the Resource Governor icon, and select New Resource Pool.
4. Enter the name of your resource pool (ResourcePoolName), the Minimum and Maximum CPU%, and the Minimum and Maximum Memory % as illustrated in Figure 16.1. Enter 20 for the minimum and 50 for the maximum CPU, and for memory enter the same values.
5. Click the OK button.
Your resource pool is now created. You will need to create some workload groups in it to make it useful.
To create a resource pool using TSQL commands, use the following syntax:
CREATE RESOURCE POOL ResourcePoolName
WITH
(
MIN_CPU_PERCENT = 20,
MAX_CPU_PERCENT = 50,
MIN_MEMORY_PERCENT = 20,
MAX_MEMORY_PERCENT = 50
)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
The value for MIN_CPU_PERCENT is the minimum CPU you want to dedicate to workloads in this resource pool. This value will not be shared among other resource pools. The value will be between 0 and 100 and must be less than the MAX_CPU_PERCENT. If you attempt to set the minimum larger than the maximum, you will get the following error message:
Msg 10908, Level 15, State 4, Line 5
Attribute 'max_cpu_percent' with value of 50 is less than
attribute 'min_cpu_percent' with value of 60.
The value for MAX_CPU_PERCENT is the maximum value of CPU you want to share among work loads using this resource pool. Note that this will not be the effective maximum CPU percent; the effective maximum will be the MAX_CPU_PERCENT less the minimum CPU percent values for all other resource pools.
When deriving values for max and min CPU, you will need to measure the CPU taken by these workloads using Profiler for a single execution and then multiply this value by the expected number of executions per second.
MIN_MEMORY_PERCENT is the minimum memory dedicated to this resource pool. The range is between 0 and 100 and must be less than the value assigned for the MAX_MEMORY_PERCENT. Estimating representative values for minimum and maximum memory is difficult. The best way to do this is to create workload groups that run in the default resource pool and measure the total and average memory taken by individual workloads over several days and then use these results for your minimum and maximum memory percentages. Use the performance monitor counter SQL Server:Resource Pool Stats:Default to get these values.
MAX_MEMORY_PERCENT is the maximum memory to dedicate to your resource pool. As with MAX_CPU_PERCENT, the effective maximum memory is the MAX_MEMORY_PERCENT less the MIN_MEMORY_PERCENT settings of all the other resource pools.
After you have configured your resource pool settings, you will need to apply them to Resource Governor by running the following command:
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
To create a workload group, follow these steps:
1. In SQL Server 2008 Management Studio, connect to a SQL Server Instance.
2. Expand the Management folder, expand the Resource Governor node, and expand the Resource Pools folder.
3. Expand the resource pool in which you want to create your workload group, right-click on the Workload Groups folder, and select New Workload Group, as illustrated in Figure 16.2.
4. In the Resource Pools section, highlight your resource pool (in this case the resource pool named ResourcePoolName), and then in the bottom part of the dialog titled Workload Groups for resource pool: ResourcePoolName, enter the name of your workload group. Enter BackupGroup. For the rest of the settings in this area, enter the following values, as shown in Figure 16.2:
For Importance, select Low.
For Maximum Requests, select 0.
For CPU Time, select 0.
For Memory Grant %, select 25.
For Grant Timeout, select 0.
For Degree of Parallelism, select 0.
The following list gives additional details about the meaning of these settings:
Importance— The Importance setting is the relative importance of a request in the workload group. Available settings are Low, Medium, and High. Importance does not imply a priority as we normally understand it in the context for threading and the process priority you see in Task Manager when you right-click on a process in the Processes tab, and select Set Priority.
For Resource Governor, Importance is a simple weighting schema among active workers for the pool. SQL Server will at any one time be simultaneously executing several tasks. Tasks that are ready to run will be in a runable task queue. When a worker is added to the runable queue, the Importance is used as a factor for position in the list against other workers in the same pool on the same scheduler. The Importance does not carry across multiple schedulers nor does it carry across multiple pools on the same scheduler. It only applies to active workers of groups assigned to the same pool.
Maximum Requests— Specifies the maximum number of simultaneous requests that are allowed to execute in the workload group. A setting of 0 indicates unlimited requests.
CPU Time (sec)— Determines how much CPU time a request can use. 0 indicates unlimited time. The values are in seconds.
Memory Grant %— The maximum amount of memory a single request can take from the resource pool.
Grant Time-out (sec)— Refers to the maximum time a query will wait for resources to become available before timing out. Values are in seconds and can range from 0 and above.
Degree of Parallelism— This setting controls the degree of parallelism, 0 meaning all processors may be used to return or manipulated large results sets. You can select a value between 0 and the maximum number of processors you have on your server. The maximum value is 64, which corresponds to the number of processors on the Wintel platform. Although parallelism can results in faster generations of results sets, it typically means high CPU consumption, which may degrade overall performance. Select a value that is best for your system.
To create a workload group using TSQL, use a command similar to the following:
CREATE WORKLOAD GROUP BackupGroup1
WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0)
USING [ResourcePoolName]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
In the preceding example, we are creating a workload group called BackupGroup, and placing it in the resource pool ResourcePoolName.
While using Resource Governor, you will find it necessary to modify your classifier function to move workloads in and out of workload groups as your workload changes to consume more or fewer resources. Initially you will need to place some workloads in the default workload group until you understand that workload’s memory consumption, and then move it to another more appropriate workload group, sometimes in a different resource pool.
To make these changes you can use SQL Server Management Studio, or use TSQL commands.
Here is an example of how to do this using SQL Server 2008 Management Studio.
1. Using SQL Server 2008 Management Studio, connect to a SQL Server instance.
2. Expand the Management folder, expand Resource Governor, expand the Resource Pools folder, right-click on your resource pool (called ResourcePoolName), and select Properties.
3. A dialog similar to the one you saw in Figure 16.1 will appear. You can make changes to the ResourcePoolName resource pool. For example, increase the minimum CPU to 25 percent.
4. Then click OK to reconfigure your resource pool to use this new minimum for CPU.
In TSQL you would issue the following command:
ALTER RESOURCE POOL [ResourcePoolName] WITH (min_cpu_percent=25)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
To make changes to your classifier function, you can use the ALTER FUNCTION command, and then issue a call to ALTER RESOURCE GOVERNOR RECONFIGURE. This must be done in the query pane; you can’t make any modifications through the Resource Governor menu items that show up in SQL Server 2008 Management Studio.
You will want to modify your classifier function when you need to change the workload group in which workloads will run.
To make changes to your workload groups, follow these steps:
1. Using SQL Server 2008 Management Studio, connect to a SQL Server instance.
2. Expand the Management folder, expand Resource Governor, expand the Resource Pools folder, expand your resource pool ResourcePoolName, expand the Workload Groups folder, and then right-click on your workload group (BackupGroup) and select Properties.
3. A dialog similar to the one you saw in Figure 16.2 will appear. You can make changes to the test workload group. For example, decrease the Importance to Low.
4. Then click OK to reconfigure your workload group to use this new Importance setting.
To make this change using TSQL, you would use the following commands:
ALTER WORKLOAD GROUP [BackupGroup] WITH (group_max_requests=0,
importance=Low,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0)
GO
In addition, it is possible to script configuration settings associated with Resource Governor. This can be achieved by right-clicking the Resource Governor folder in SQL Server Management Studio and selecting the appropriate statement.
Resource Governor needs to be closely monitored to get maximum benefit from it.
The three tools you can use to do this are
Reliability and Performance Monitor
Profiler
Dynamic Management Views (DMVs)
In Performance Monitor, use the counters SQLServer:WorkloadGroup Stats and SQLServer:Resource Pool Stats to monitor the active workloads in a workload group or resource pool. If one workload group consumes a significant portion of the resources in the default resource pool, you will need to migrate it to its own resource pool to minimize the impact on the other works loads in this Workoad Group or resource pool.
Of the counters in the SQLServer:WorkloadGroup Stats object, the following will be most useful to you:
Active Parallel Threads— Number of threads used by parallel queries in the workload group.
Active Requests— Number of currently running requests in the workload group.
Blocked Tasks— Number of blocked tasks in the workload group.
CPU Usage— System CPU usage by all requests in this workload group.
Max Request CPU Time (ms)— Maximum CPU time in milliseconds used by a request in the workload group.
Max Memory Grant (KB)— Maximum value of memory grant in kilobytes used by a query in the workload group.
Queued Requests— Number of requests waiting in the queue due to resource governor limits in the workload group.
Reduced Memory Grants/sec— Number of queries per second getting a less than ideal amount of memory in the workload group.
Requests completed/sec— Number of completed requests per second in the workload group.
Of the counters in the SQLServer:Resource Pool Stats object, the following will be most useful to you:
Active memory grant amount (KB)— Total amount of granted memory in kilobytes in the resource pool.
Active memory grant count— Number of query memory grants in the resource pool.
CPU Usage %— System CPU usage by all requests in the specified instance of the performance object.
CPU Usage Target %— Target value of “CPU usage %” for the resource pool based on the configuration settings and the system load.
Max Memory— Maximum amount of memory in kilobytes that the resource pool can have based on the settings and server state.
Memory grant timeouts/sec— Number of query memory grant timeouts per second occurring in the resource pool.
Memory grant/sec— Number of query memory grants per second occurring in the resource pool.
Pending Memory grants count— Number of queries waiting for memory grants in the resource pool.
Query exec memory target— Current memory target for query execution memory grant in kilobytes.
Target Memory— Target amount of memory in kilobytes that the resource pool is trying to attain based on the settings and server state.
Used Memory— Amount of memory used in the resource pool in kilobytes.
Figure 16.3 illustrates the CPU Usage % counter for the SQLServer:Resource Pool Stats objects. In this figure there are three resource pools. The first pool uses maximum CPU, until a workload in the second pool requires CPU resources and Resource Governor throttles the first workload to give the second resource pool its maximum. Then the third resource pool requires resources and both other resource pools are throttled.
In Profiler, use the events CPU Threshold Exceeded, PreConnect:Starting, and PreConnect:Completed. CPU Threshold Exceeded will tell you when the Request_Max_CPU_TIME_SEC thresholds are exceeded for workloads. The PreConnect counters will tell you when the classifier function is operating.
The following DMVs will give you a window into Resource Governor, the resource pools and the workload groups.
Sys.dm_resource_governor_workload_groups— Returns workload group statistics and the current in-memory configuration of the workload group. The most significant columns that are returned by this DMV are the following:
Name— The name of the workload group.
pool_id— The ID of the pool in which the workload group is located.
total_request_count— The total number of requests for this workload group.
total_queued_request_count— The total number of requests queued in this workload group. This is an indication that some workloads in this workload group are being throttled.
active_request_count— The number of active requests in this workload group.
queued_request_count— The number of queued requests in this workload group.
total_cpu_limit_violation_count— The number of requests exceeds the maximum CPU limit.
total_cpu_usage_ms— The total CPU usage by workloads in this workload group.
max_request_cpu_time_ms— The maximum CPU usage for a single request in milliseconds.
blocked_task_count— The number of blocked tasks.
total_lock_wait_count— Cumulative number of lock waits.
total_lock_wait_time_ms— Total time spent waiting for locks.
total_reduced_memgrant_count— Cumulative count of memory grants that reached the maximum query size limit.
max_request_grant_memory_kb— Maximum memory grant size, in kilobytes, of a single request since the statistics were reset.
active_parallel_thread_count— Current count of parallel thread usage.
importance— Current configuration value for the relative importance of a request in this workload group
request_max_memory_grant_percent— Current setting for the maximum memory grant, as a percentage, for a single request.
request_max_cpu_time_sec— Current setting for maximum CPU use limit, in seconds, for a single request.
request_memory_grant_timeout_sec— Current setting for memory grant time-out, in seconds, for a single request.
group_max_requests— Current setting for the maximum number of concurrent requests.
max_dop—maximum degree of parallelism
This DMV will return a wealth of information about requests in each group. The following query will return a list of requests in each workload group:
SELECT r.group_id, g.name, r.status, r.session_id,
r.request_id, r.start_time, r.command, t.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = r.group_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name
GO
Sys.dm_resource_governor_resource_pools— Returns information about the current resource pool state, the current configuration of the resource pools, and the resource pool statistics.
Sys.dm_resource_governor_configuration— Returns a row that contains the current in-memory configuration state of Resource Governor.
This DMV will return two columns, classifier_function_id, and is_reconfiguration_pending. The classifier_function_id matches with the object_id column in sys.objects. A value of 0 for is_reconfiguration_pending confirms that there is no pending reconfiguration of the Resource Governor due to changes in configuration, and the Resource Governor configuration metadata matches its in-memory configuration.
Here is an example query illustrating usage of sys.dm_resource_governor_configuration, returning the name of the classifier function:
select object_schema_name(classifier_function_id) +'.'+
OBJECT_NAME(classifier_function_id) AS
ClassifierFunction, is_reconfiguration_pending
You can use the following query to find out the name of the workload group and the resource pool, in Resource Governor, that the classifier function assigned to each session of a Microsoft SQL Server instance:
SELECT session_id as 'Session ID',
[host_name] as 'Host Name',
[program_name] as 'Program Name',
nt_user_name as 'User Name',
SS.[Name] as 'Group Assigned',
SES.[name] as 'Pool Assigned'
FROM sys.dm_exec_sessions SES
INNER JOIN
sys.dm_resource_governor_workload_groups SS
ON SDES.group_id = SDRGWG.group_id
INNER JOIN sys.dm_resource_governor_resource_pools DRGRP
ON SDRGWG.pool_id = DRGRP.pool_id
WHERE Session_ID>50
The following query will tell you how the classifier function is classifying workloads.
select Sessions.session_id, Sessions.group_id,
CONVERT(NCHAR(20), Groups.name)
as group_name from sys.dm_exec_sessions as Sessions
join sys.dm_resource_governor_workload_groups as Groups
on Sessions.group_id = Groups.group_id where session_id > 50
This query will display which CPU/Scheduler each workload/session is running on.
select Requests.session_id,
CONVERT(NCHAR(20), WorkLoadGroup.name) as group_name,
Tasks.scheduler_id,
Requests.status
from sys.dm_exec_requests Requests
join sys.dm_os_tasks Tasks on Requests.task_address =
Tasks.task_address
join sys.dm_resource_governor_workload_groups WorkLoadGroup on
Requests.group_id = WorkLoadGroup.group_id
where
Requests.session_id > 50
GO
Resource Governor is a new feature in SQL Server 2008 that allows DBAs to selectively throttle workloads so that resource-hungry workloads will not affect the performance of other processes on the box. Take care creating resource pools and workload groups so that you can get optimal performance out of your Resource Governor solution.
Try to estimate the CPU and memory resources that workloads will consume and place them in a workload group that approximates this load. Monitor the resource consumption in using the DMVs and adjust the workload group settings if appropriate. Create a new resource pool if necessary.
For workloads whose characteristics are unknown, place them in the default workload group for the default resource pool and monitor its resource consumption there. Create a new resource pool or Workload group if necessary and move it there if required.
Ensure that your classifier function performs well, and if you need to use a large lookup table, ensure that there are covering indexes in place on it.
Ensure that any application using Resource Governor has retry/recovery logic built into it, so that if a grant request should fail there will be no data loss.