In SQL Server 2014, how do you suppress your storage with resource governor?

In today's article, I'd like to talk about the cool boost in SQL Server 2014: Now you can finally suppress queries based on the IOPS you need! The resource governor (Resource Governor) was introduced from SQL Server 2008, but the functionality offered is limited: You can only limit CPU time (which is already great), and you can limit the amount of queries (from each independent query) to the memory.

But as a DBA, you often do some database maintenance operations, such as index rebuilds, DBCC CHECKDB operations, and so on. As we all know, these operations will bring a lot of iops to the peak in your storage. If you have a 7 * 24 online database, this will affect your productivity and have a significant impact on your business and sales.

This has changed since SQL Server 2014, because you can use resource governor to deploy a specified pool of resources to limit your IOPS utilization. When you isolate your DBA operation to the specified resource pool, you can specify the maximum IOPS (including minimum iops) that the resource pool can use. So you can suppress the IOPS required for DBA operations. Your production workload will be able to better use your storage. More information can be found on Microsoft online Help.

I would like to use a very simple example to illustrate this behavior. Assuming that you are a DBA, you are about to perform a regular index rebuild operation, which requires a resource governor to control their maximum IOPS utilization. 1th Step we create dedicated resource pools and workload groups for DBA operations.

1-Create a new Resource Pool for the DBAs.
 2-We use a very high value for MAX_IOPS_PER_VOLUME so that we are
 3-currently running unlimited.
 4 CREATE RESOURCE POOL DbaPool WITH
 5 (
 6 MAX_IOPS_PER_VOLUME = 100000
 7)
 8 GO
 9 
10-Create a new Workload Group for the DBAs
11 CREATE WORKLOAD GROUP DbaGroup
12 USING DbaPool
13 GO
 As you can see from the code just now, the CREATE RESOURCE POOL statement now provides you with the MAX_IOPS_PER_VOLUME attribute (including MIN_IOPS_PER_VOLUME). Here I set a very high value, so the IOPS will not be limited during the first execution, here we have established an initial baseline based on the required IOPS. In the next step I will create the classification function required by the resource governor.

 1-Create a new Classifier Function for Resource Governor
 2 CREATE FUNCTION dbo.MyClassifierFunction ()
 3 RETURNS SYSNAME WITH SCHEMABINDING
 4 AS
 5 BEGIN
 6 DECLARE @GroupName SYSNAME
 7
 8 IF SUSER_NAME () = ‘DbaUser’
 9 BEGIN
10 SET @GroupName = ‘DbaGroup’
11 END
12 ELSE
13 BEGIN
14 SET @GroupName = ‘Default’
15 END
16
17 RETURN @GroupName;
18 END
19 GO
 In the classification function, we evaluate based on login. If the login is DbaUser, the incoming session will be in the DbaGroup workload group. Otherwise, enter the default workload group. Finally, we register and configure it in the resource governor, so that our settings take effect.

 1-Register the Classifier Function within Resource Governor
 2 ALTER RESOURCE GOVERNOR WITH
 3 (
 4 CLASSIFIER_FUNCTION = dbo.MyClassifierFunction
 5)
 6 GO
 7
 8-Reconfigure Resource Governor
 9 ALTER RESOURCE GOVERNOR RECONFIGURE
10 GO
Now when you create a login called DbaUser, you can use it to connect to your SQL Server. You can check the group_id column in DMV sys.dm_exec_sessions to verify that the incoming session is in the correct workload group. Next, I create a non-clustered index in the DataKey in the FactOnlineSales table of the ContoRetailDW database.

1-Create a simple Non-Clustered Index
2 CREATE NONCLUSTERED INDEX idx_DateKey ON FactOnlineSales (DateKey)
3 GO
We created a resource pool from the beginning, and now we have no restrictions in our resource pool. Therefore, when we now rebuild the index of the non-clustered index we just created, SQL Server will consume a lot of IOPS. We can verify the resource pool just created through the performance counter "SQL Server: Resource Pool Stats: Disk Write IO / Sec" performance counter.

1 ALTER INDEX idx_DateKey ON FactOnlineSales REBUILD
2 GO
It can be seen that the index rebuilding cost nearly 100 IOPS. The next thing I want to do is to limit the DbaPool resource pool to only 50 IOPS:

1-Let ‘s change the Resource Pool by lowering the maximum IOPS.
2 ALTER RESOURCE POOL DbaPool WITH
3 (
4 MAX_IOPS_PER_VOLUME = 50
5)
6 GO
Now when you perform an index rebuild, you can clearly see in the performance monitor that there is only an average of 50 IOPS in a particular resource pool.

 

In addition, the Disk Write IO Throttled / sec performance counter will also tell you the number of IOPS limits for your resource governor.

Using the previous resource governor, there is no way to check whether it is suppressed. This is also a very important factor for performance tuning. When the resource governor is enabled, no specific wait types appear in SQL Server. My testing shows that once the resource governor is enabled, there are no more PAGEIOLATCH_SH / PAGEIOLATCH_EX wait types, which is right. The following two pictures show the specific wait type information for the session where the index rebuilding occurs-the first one does not have a resource governor, and the second one has a resource governor suppressing IOPS.


 

As you can see from the two figures, there is a huge difference between the two running tests, especially in the PAGEIOLATCH_EX and SOS_SCHEDULER_YIELD wait types.

From my standpoint of IOPS suppression, the resource governor is a good addition to the existing functions, which makes the resource governor more mature. You can try to solve IOPS problems with this new feature.

Thanks for attention!

In SQL Server 2014, how to suppress your storage with resource governor?