SQL Server 2014 Resource Governor – Is Write IO throttled?

Hi Friends,

In this post I’m going to talk about SQL Server 2014 Resource Governor IO throttled.

Resource Governor was introduced as an Enterprise Edition feature in SQL Server 2008 allowing DBAs to control/throttle CPU & Memory usage for a given workload or a set of workloads. There has been incremental enhancements to this feature in SQL Server 2012.

In SQL Server 2014, SQL Server team adds IO throttling to Resource Governor.

Indeed a wonderful enhancement, especially for hosting companies or enterprises that offer database as a service.

While IO governance is primarily designed to throttle read workload, there is some confusion or misunderstanding that the feature does not throttle Write IO.

In this quick demo, I want to show that Resource Governor in SQL Server 2014 throttles Write IO, with some caveats.

Let’s begin.

I have 3 databases called RebublicansDB, DemocratsDB and TechedAttendeesDB. Each database has two tables, factproductinventory (a blown up version from AdventureWorksDW) and another simple table called LoadData. The LoadData table is a simple table that accepts default values for all the columns.

The above setup is part of my larger demo. In this post, I am only going to use TechedAttendeesDB database to demonstrate write IO throttling

Next, I have setup the following Resource Governor Pools and Workload Groups:

Pools: DemocratsPool, RepublicansPool, TechedAttendeesPool

Workload Groups: DemocratsGroup, RepublicansGroup, TechedAttendeesGroup

So, each Pool has a Workload Group.

And I have setup a simple classifier function where the incoming query is redirected to the appropriate workload group depending on the Original Database Name.

Action Time:

I start the write workload with a simple looping script:

-- change connection to TechEdAttendeesDB
WHILE 1=1
BEGIN
    INSERT LoadData DEFAULT VALUES
END
GO

And let us observe the Disk Write IO Throttled/sec & Disk Write IO/sec counters under Resource Pool Stats object in Performance Monitor:

Surprisingly, you will observe that both counters stay at 0 (zero). Disk Write IO Throttled/sec remains at 0 as there is no throttling (and I will digress more into it in a moment). But Disk Write IO/sec also does not show any value simply because the new pages being allocated remain in the buffer pool and unless a manual CHECKPOINT statement is issued, it will remain at 0. Note that I said “manual CHECKPOINT”, since an automatic CHECKPOINT issued by the system is an internal process and will go to Internal Pool. When we issue a manual CHECKPOINT, the check-pointing code runs under the user context.

Stop the loop. Now let us modify the code and insert a manual CHECKPOINT statement and execute:

-- change connection to TechEdAttendeesDB
WHILE 1=1
BEGIN
    CHECKPOINT
    INSERT LoadData DEFAULT VALUES
END
GO

And now you will observe IOPs showing up:

In my case here, it touches an upper boundary of 800 or so and a lower mark of 300 or so. The screenshot I captures shows 553.

Now this is making sense. The CHECKPOINT now runs under the user context and comes under the user defined pool of TechedAttendees and the values show up in the counter. However Disk Write IO Throttled/sec remains at 0 as throttling is still not happening because default MAX_IOPS_PER_VOLUME for a Pool is 2147483647 and 553 is way lower than that.

What I mean is, when we create the Pools, it is as good as this code:

ALTER RESOURCE POOL TechEdAttendeesPool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);

Now, we will restrict as follows:

ALTER RESOURCE POOL TechEdAttendeesPool WITH (MAX_IOPS_PER_VOLUME=100)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

I intentionally put a value of 100 since I know my lower mark was 300. I will now expect that Disk Write IO/sec will not cross beyond 100.

Execute the loop again.

And yes, rightly so, we can see the write IO being throttled real-time and we have governed the IO not to cross 100 for the given workload.

Conclusion: Resource Governor in SQL Server 2014 throttles write IOs, however these caveats should be known.

Hope you like the post!