Tip of the Month: Resource Governor for I/O in SQL 2014

Available in SQL Server 2014 Enterprise Edition

The Resource Governor can now restrict the number of I/O’s sent to the disk subsystem. In the past, only CPU and Memory could be throttled, but now DBAs can also throttle I/O. This gives DBAs the ability to further control those users who insist on running reports or other queries that can bring all other activity on the instance to a grinding halt.

Here is what you can now do with the Resource Governor:

• Set constraints on the physical I/O operations
• Throttles I/O by controlling the number of I/O’s sent to the disk subsystem
• I/O’s can be reads or writes of any
• Limit the physical I/O’s issued for user threads in a resource pool

MAX_OUTSTANDING_IO_PER_VOLUME = set the maximum outstanding I/O operations per disk volume. Can be set and act as a safety even when MAX_IOPS_PER_VOLUME and MIN_IOPS_PER_VOLUME have been set.

ALTER RESOURCE GOVERNOR

WITH (MAX_OUTSTANDING_IO_PER_VOLUME = 20)

MAX_IOPS_PER_VOLUME and MIN_IOPS_PER_VOLUME = the maximum and minimum IO operations per second respectively

CREATE RESOURCE POOL ReportPool

WITH (MIN_IOPS_PER_VOLUME = 20, MAX_IOPS_PER_VOLUME = 60)

More info can be found at:

https://msdn.microsoft.com/en-us/bb933866.aspx

http://www.databasejournal.com/features/mssql/restricting-io-using-sql-server-2014-resource-governor.html