SQL Server 2014 added the new IO Resource Governor feature in addition to the existing CPU and Memory governor pools. Resource Governor in general enables us to distribute server resources among resource pools. Now in SQL Server 2014 we not only have the option to deal with CPU and Memory but also the capability of distributing IO.
We could achieve that by simply adding these two statements to our resource pool.
MIN_IOPS_PER_VOLUME =value : Specifies a value for the minimum I/O operations per second (IOPS) per disk volume for workloads and groups of workloads.
MAX_IOPS_PER_VOLUME =value : Specifies a value for the maximum I/O operations per second (IOPS) per disk volume for workloads and groups of workloads.
The total sum of minimums (non-shared) across all resource pools must not exceed 100% and the maximums indicate the shared portion of the shared portion of the pool.
One thing I would like to mention is the template for resource in the pre-release the IO arguments are not included for it. I am sure MS will make changes to the template in the actual release.
Let us change our copy for it. If you haven’t work on any SQL Server templates keep reading otherwise you may just jump to step 2
Step 1: Click on View menu, Template Explorer or Press CTRL + ALT + T, In the left side template Browser will open, Expand the Resource Governor template, To check what parameters it has the simplest thing will be to click on the toolbar button Specify Values for Template Parameters. As you can see we still only have parameters for CPU and Memory but not IO.
Step 2: Let us add the IO parameters first right click on the template and click on Edit then add these two lines of script for the IO and set your default min and max as you wish I set mine 10% for both.
,MIN_IOPS_PER_VOLUME = <MIN_IOPS_PER_VOLUME ,int, 10>
,MAX_IOPS_PER_VOLUME = <MAX_IOPS_PER_VOLUME ,int, 10>
Once we finish adding that if we again click on Specify Values for Template Parameters button from the tool bar we will see the IO parameters are added for your template.
We have several system views, DMVs, new performance counters, and xEvents to monitor resource governor. Check out the white paper “SQL Server 2014 and the Data Platform” from the link given in the reference section for detail