SQLRx Tip of the Month
 
August 2015
______________________________________________________________________________
SQL 2008

SQL Server Administration:  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
  • 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 


SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
As always, don't forget to regularly monitor the 11 VitalSigns  used by SQLRx to debug performance bottlenecks.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].


 

Phone: 214.526.7680 x 113

Toll free:  800.85.SQLRx  (x113)

blog.sqlrx.com

@SQLRx
 


 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit


 

30 Years of Credibility & Trust


 

Join our email list          Check out our events          SQLRx Downloads