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
- Throttle 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 size
- Limit the physical I/O's issued for user threads in a given 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 I/O 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