SQL2014 governs IO!

The release of SQL 2014 CTP1 has brought IO to the list of resources it can govern! (yes, previously it was memory and cpu only.) Lets get down to business and run through a quick demo so we can get a sneak peek of SQL server 2014 metering IO!

 For the sake of simplicity in this demo, we will do 3 things:

 1) Create two resource pools, each with their associated workloadgroup, and we will configure the max_iops value only. No throttling of memory or cpu will take place.

2) The users will each will have a login created, and under the context of those logins, we will generate some IO against the system volume (C:)

3) We will review some DMO’s and analyze the results using perfmon.

First create two new resource pools and set the max_iops_per_volume value. Note, there is no GUI for this in SQL 2014 CTP, so you will have to use TSQL. I have shown the code below. Just repeat this code twice for your resource pools and change out the max iops value.

resourcepool

I have randomly chosen to assign a low number so that we can quickly see the throttling take place. As you can see my pool name is ‘users’ and i have set max_iops to 100. My second resource pool is named ‘developers’ and i have given it a max_iops_per_volume value of 50.

Next, we need to create 2 workload groups which will be assigned to these resource pools. These can be done through GUI or scripts.

workloadgroup

Then we create and assign a classifier function, and the logins. (I wont show this here as nothing has changed since the last version of SQL. Please email me if you would like a copy of the scripts that were used.)

resource governor gui

Now we need to open two new query windows and log into each using the ‘users’ and ‘developers’ credentials. In each window we will run a DBCC CHECKDB on two copies of the Adventureworks2012 database in order to generate some IO.

workload

Open Performance monitor to see how the IO has been throttled. The results look like this. (Note the performance counters that we are using)

perfmon

perfmon

The first thing that i notice is that throttling is clearly taking place. The second thing that i notice however is that the max_iops values that i have set are not being reached consistently. I suspect that a lot of CHECKDB is happening in memory after the initial process begins as AdventureWorks is quite small, a more IO specific workload and tuning of the max_iops could see a more consistent result set here.

Lets have a look at a couple of DMO’s. Microsoft has kindly added several new fields to the sys.dm_resource_governor_resource_pools. You can see here the reads_total versus the reads_throttled. (Note that this System view is not populated until you restart your instance, and that this is only a subset of the newly available columns.)

dmv

Lets also checkout sys.dm_resource_governor_resource_pool_volumes!

dmv

Here you can see the volumes that are in use by our resource pools, and also a taste of some of the other new columns added. Notice that the ‘developers’ login experienced much more throttling. (This DMV is populated once a user connection is made through Resource Governor.)

So there we have it, a first look at resource governors new IO capability. I have done a very basic example here and if you would like to test further you could scale out by increasing the workload, reducing concurrency, and playing with different values for min and max iops.

Posted on: July 22, 2013, by : admin

Leave a Reply