(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan, where you are getting week by week via email all the essential knowledge you need to know about performance tuning on SQL Server.)

In todays blog posting I want to talk about a very cool enhancement in SQL Server 2014: you are now finally able to throttle queries regarding their needed IOPS! Resource Governor was introduced back in SQL Server 2008, but the feature set was somehow limited: you were just able to limit CPU times (which is already awesome), and you were able to limit the amount of Query Memory that can be taken from an individual query.

But just imagine you are a DBA, and you are (hopefully) performing some database maintenance activities, like index rebuild, and regular DBCC CHECKDB operations. As you know these operations will cause a huge amount of IOPS in your storage an will drive it up to the maximum. But when you are running a 24/7 SQL Server installation this will directly impact your production workload that drives your business and where you are making your money. 

Things are changing now with SQL Server 2014, because you can limit the IOPS usage in a specific Resource Pool that is deployed through Resource Governor. When you are isolating your DBA activities into a specific Resource Pool, you are just able to specify the maximum IOPS (besides the minimum IOPS) that this Resource Pool should have. So you are able to throttle down the IOPS that your DBA activities needs. And your production workload will have a better utilization of your storage. You will find further information about that functionality on Books Online.

I want to show you this behavior with a very simple example. We are just assuming that we are DBAs, who are performing regularly index rebuild operations, that have to be controlled through Resource Governor for their maximum IOPS usage. In the 1st step I’m creating a dedicated Resource Pool and Workload Group for the DBA operations.

-- Create a new Resource Pool for the DBAs.
-- We use a very high value for MAX_IOPS_PER_VOLUME so that we are
-- currently running unlimited.
CREATE RESOURCE POOL DbaPool WITH
(
MAX_IOPS_PER_VOLUME = 100000
)
GO
-- Create a new Workload Group for the DBAs
CREATE WORKLOAD GROUP DbaGroup
USING DbaPool
GO

As you can see from the previous listing, the CREATE RESOURCE POOL statement offers you now the property MAX_IOPS_PER_VOLUME (besides MIN_IOPS_PER_VOLUME). I’m just setting here a very high value, so that the IOPS are not limited in the first run, where we are establishing our initial baseline regarding the needed IOPS. In the next step I’m creating the Classifier Function that is needed by Resource Governor.

-- Create a new Classifier Function for Resource Governor
CREATE FUNCTION dbo.MyClassifierFunction()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME

IF SUSER_NAME() = 'DbaUser'
BEGIN
SET @GroupName = 'DbaGroup'
END
ELSE
BEGIN
SET @GroupName = 'Default'
END

RETURN @GroupName;
END
GO

Inside the Classifier Function I’m just evaluating the login. If the login is DbaUser, then the incoming session is routed into the workload group DbaGroup. Otherwise the session is placed into the default workload group. And finally we are registering the Classifier Function within Resource Governor and reconfiguring it, so that our changes are live.

-- Register the Classifier Function within Resource Governor
ALTER RESOURCE GOVERNOR WITH
(
CLASSIFIER_FUNCTION = dbo.MyClassifierFunction
)
GO
-- Reconfigure Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

When you have now created a new login called DbaUser, you are able to connect with it to your SQL Server. You can also cross check through the column group_id in the DMV sys.dm_exec_sessions if the incoming session was routed in the correct workload group. In the next step I’m creating a simple Non-Clustered Index on the column DateKey of the table FactOnlineSales of the database ContosoRetailDW.

-- Create a simple Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_DateKey ON FactOnlineSales(DateKey)
GO

As you know from the beginning where we have created our Resource Pool, we are currently not restricted on our IOPS. So when we are now doing a rebuild of the previous created Non-Clustered Index, SQL Server will take a lot of IOPS. To prove you can open Performance Monitor and watch the performance counter “SQL Server:Resource Pool Stats:Disk Write IO/Sec” of the previous created Resource Pool.

ALTER INDEX idx_DateKey ON FactOnlineSales REBUILD
GO

The following picture shows the output from my system.

As you can see the Index Rebuild takes around 100 IOPS. What I’m doing now in the next step is to restrict the Resource Pool DbaPool to only 50 IOPS:

-- Let's change the Resource Pool by lowering the maximum IOPS.
ALTER RESOURCE POOL DbaPool WITH
(
MAX_IOPS_PER_VOLUME = 50
)
GO

When you are now rerunning the index rebuild, you can see very nicely in Performance Monitor, that we have on average in that specific Resource Pool only the 50 IOPS:

In addition the performance counter Disk Write IO Throttled/sec also tells you how many IOPS were throttled by Resource Governor.

As with the previous versions of Resource Governor, the query itself has no idea, if it was throttled down or not. This is also a very important fact for performance troubleshooting. There are also no specific wait types, that are reported in SQL Server, when Resource Governor kicks in. My tests have shown that SQL Server reports much more PAGEIOLATCH_SH/PAGEIOLATCH_EX wait types as soon as Resource Governor kicks in, which makes sense. The following 2 pictures are showing you the accumulated wait types for the session where the index rebuild happened – the 1st picture without Resource Governor, and the 2nd picture where Resource Governor throttled the IOPS.

As you can see from both pictures, there is a really huge difference between both tests runs, especially at the wait types PAGEIOLATCH_EX and SOS_SCHEDULER_YIELD.

From my perspective the IOPS throttling in Resource Governor is a very nice addition to the already existing functionality, and make Resource Governor just more mature. And I know a lot of companies and even customers, who will solve a lot of their problems with that new functionality.

Call to action: please feel free to comment how you think about that functionality, and if you have use cases where you can use it.

Thanks for reading

-Klaus