Use SQL Server 2014 Resource Governor to Throttle Processes for IO Usage

By: Mohamed Mawla   |   Updated: 2014-10-01   |   Comments (6)   |   Related: > Resource Governor

Problem

Microsoft introduced Resource Governor in SQL Server 2008 Enterprise Edition with the capability to control CPU and memory resource consumption by setting limits through workload groups. By assigning a workload group to processes that share the same characteristics (reporting, maintenance, certain users, etc.), SQL Server can throttle resources usage as required. However, there was no way to limit or throttle IO resources usage; something that is important in cases like Table Scans, Database maintenance tasks, etc.

Solution

SQL Server 2014 addressed the gap of throttling IO resources by adding support for IO resource throttling in Resource Governor. We can now specify minimum and maximum IOPS (Input/Output Operations per Second) per disk volume in the resource pool definition. MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME are the minimum and maximum physical IO operations per second (IOPS) per disk volume for a resource pool. Related monitoring goodies have also been added including : new Perfmon counters, the sys.dm_resource_governor_resource_pool_volumes DMV to return IO usage stats about volumes used by the instance, new Extended Events in addition to adding extra columns to some existing DMVs.

As stated before, throttling IO resources usage can become handy in cases where the IO subsystem is used heavily and not necessarily something like memory consumption such as with index maintenance, DBCC's , etc. where they can have lower priority than some business processes. For the record, there are some alternatives for running integrity checks against databases such as running on a standby instance or running against secondary replica, but it is beyond the scope of this tip and it is STILL important to run integrity checks on primary databases to catch any problems with the respective IO subsystem.

Basic Steps to Setup SQL Server Resource Governor

Let's say we have a weekly database maintenance process that we are not very picky about the duration it takes.  Instead we are more concerned about user sessions and we do not want the database maintenance to affect user performance. Now with resource governor we can limit the max IOS per volume for something like database maintenance tasks.

Here is the basic process to configure Resource Governor:

1: Create a resource pool that defines the limit of resources to be used by database maintenance task.

2: Create a workload group that will use the resource(s) constraints defined in the resource pool.

3: Create a classifier function that will "route" the processes to the respective workload groups based on conditions like the application name or login. Warning:  You really want to make your classifier function as simple as possible since it will be evaluated for every new session, even when connection pooling is enabled. A complex or badly written one can slow your applications or even bring SQL Server to a halt.

4: Alter Resource Governor to use the classifier function for incoming connections then enable Resource Governor.

Step by Step SQL Server Resource Governor Example

We will be using sample database AdventureWorks2014 to demonstrate throttling CHECKDB. The database backup is small so we want to enlarge database a bit. You can use the method described here, use your own method or a 3rd party tool.

To follow the basic rule of making our classifier as simple as possible, we will be creating a login to use exclusively for running DBCC CHECKDB. The classifier will be able to "route" the connection to a respective workload group based on the login used. With our example, make sure you have SQL Server authentication enabled.

CREATE LOGIN checkdb_maintenance WITH PASSWORD = 'checkdb_maint0!';
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [checkdb_maintenance]
GO

Create the resource pool and workload group without any specifications.

CREATE RESOURCE POOL checkdb_maintenance;
GO
CREATE WORKLOAD GROUP checkdb_maintenance USING checkdb_maintenance;
GO

Create the classifier function that will route the incoming connections to the respective workload groups and pools. The function will simply filter anything using loginname checkdb_maintenance to a group of the same name and anything else to the default pool

USE MASTER;
GO
CREATE FUNCTION dbo.Restrict_checkdb_IO()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
       DECLARE @GroupName SYSNAME
          IF SUSER_NAME() = 'checkdb_maintenance'
            BEGIN
              SET @GroupName = 'checkdb_maintenance'
           END
       ELSE
       BEGIN
              SET @GroupName = 'default'
       END
       RETURN @GroupName;
END

Assign the classifier to Resource Governor.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Restrict_checkdb_IO);

As of this moment, the new pool is not yet enabled and all user connections will be routed to the default pool even if they are using login checkdb_maintenance.

Let's reconfigure Resource Governor with the new changes.

ALTER RESOURCE GOVERNOR RECONFIGURE;

Now we can see the new pool in Perfmon and also listed in the sys.dm_resource_governor_resource_pools DMV.

SELECT pool_id, name, min_iops_per_volume, max_iops_per_volume, read_io_queued_total,
read_io_issued_total, read_io_completed_total,read_io_throttled_total, read_bytes_total,
read_io_stall_total_ms, read_io_stall_queued_ms, io_issue_violations_total,io_issue_delay_total_ms
FROM   sys.dm_resource_governor_resource_pools
WHERE  name <> 'internal'; 

Let's run DBCC CHECKDB using the login checkdb_maintenance and observe if the process will be throttled. Kindly note that we have not setup any limits on the new resource pool, it just exists. I'm using login checkdb_maintenance as I run the code below.

DBCC DROPCLEANBUFFERS
GO
DBCC CHECKDB (AdventureWorks2014 ) WITH NO_INFOMSGS; 
GO

Using perfmon, I'm watching three counters under Resource pool stats objects. Once we run the DBCC CHECKDB statement, we will see that the classifier function redirects the connection to checkdb_maintenance pool. This is a small database so the usage is not that high though.

Using DMVs, we can also get more information about the running processes and associated resource pools.

SELECT
 r.session_id, 
 r.request_id as session_request_id,   
 s.group_id,rg.name as pool_name,  
 r.status, 
 s.host_name,   
 CASE WHEN s.login_name = s.original_login_name THEN s.login_name 
 ELSE s.login_name + ' (' + s.original_login_name + ')' 
 END as login_name,
 s.program_name, 
 db_name(r.database_id) as database_name, 
 r.command, 
 substring(st.text,r.statement_start_offset/2 ,
 (CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 
 ELSE r.statement_end_offset 
 END - r.statement_start_offset)/2) as statement,
 r.start_time,
 r.total_elapsed_time as total_elapsed_time_ms,
 r.cpu_time as cpu_time_ms,
 r.wait_type as current_wait_type,
    r.wait_resource as current_wait_resource,
    r.wait_time as current_wait_time_ms,
    r.last_wait_type,
    r.blocking_session_id 
FROM   sys.dm_exec_requests r      
LEFT OUTER JOIN sys.dm_exec_sessions s 
 ON s.session_id = r.session_id         
LEFT OUTER JOIN sys.dm_resource_governor_resource_pools rg 
 ON s.group_id=rg.pool_id             
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st 
WHERE r.session_id<>@@spid 

Now, let's assume we don't want DBCC CHECKDB to surpass 600 IOPS per volume. We can do this by specifying a value for Max_IOPS_PER_VOLUME in the resource pool definition then reconfigure Resource Governor to honor the new definition.

In the code and screen shot below, we are retrying DBCC CHECKDB with the Max_IOPS_PER_VOLUME configuration of 600 and 1500 then validating the results in PerfMon.

WAITFOR DELAY '00:00:15'
GO
ALTER RESOURCE POOL checkdb_maintenance WITH (Max_IOPS_PER_VOLUME=600);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
WAITFOR DELAY '00:00:15'
GO
ALTER RESOURCE POOL checkdb_maintenance WITH (Max_IOPS_PER_VOLUME=1500);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

The DMV sys.dm_resource_governor_resource_pools will also show the throttled IO values.

Same values can be shown in DMV sys.dm_resource_governor_resource_pool_volumes.

SELECT * 
FROM  sys.dm_resource_governor_resource_pool_volumes

Next Steps

• Understanding your environment, infrastructure, application, performance demands and workload are the keys to implement Resource Governor effectively. Identifying your resource needs per process and the associated priorities can help use resources effectively. Always test your scenarios in a Development or Test environment before deploying to production.
• Check out these additional SQL Server Resource Governor resources:
  • Handling workloads on SQL Server 2008 with Resource Governor
  • Using DMVs to Adjust SQL Server Resource Governor Settings
  • Configuring Resource Governor

About the author

Mohamed Mawla works as a Database Consultant for The Pythian Group in Ottawa. His expertise spans SQL Server and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips