Use SQL Server 2014 Resource Governor to Throttle Processes for IO Usage
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.
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
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
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
- 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:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips