How do I suppress your storage with a resource governor in SQL Server 2014? _mssql

In today's article, I want to talk about a really cool promotion in SQL Server 2014: Now you can finally suppress the query based on the IOPS you need! The resource governor (Resource Governor) has been introduced from SQL Server 2008, but the functionality provided is limited: You can limit CPU time (which is already great), and you can limit the amount of queries (from each individual query).

But as a DBA, you often do some database maintenance operations, such as index rebuilding, DBCC checkdb operations, and so on. As we all know, these operations will bring a large amount of IOPS to the peak in your storage. If you're on a 7 * 24 online database, this can affect your productivity and make a big difference to your business and sales.

This has changed since SQL Server 2014 because you can use the resource governor to deploy the specified pool of resources to limit IOPS usage. When you isolate your DBA operation to a specified pool of resources, you can specify the maximum IOPS (including minimum iops) that the resource pool can use. So you can suppress the IOPS required by the DBA operation. Your production workload will make it easier to use your storage. For more information, see Microsoft Online Help.

I want to show this behavior in a very simple example. Assuming you are a DBA, you are about to perform a general indexing rebuild operation, which requires the resource Governor to control their maximum IOPS usage. The 1th step is to create a dedicated resource pool and workload group for the DBA operation.

--Create A new Resource Pool for the DBAs.
--we use a very high value for Max_iops_per_volume so 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 code just now, theCREATE RESOURCE POOL statement provides you with Max_iops_per_volume attributes (including Min_iops_per_volume). Here I set a very high value, so the IOPS will not be limited at the first execution, where we set up the initial baseline based on the ioPS we need. Next I will create the classification function that the resource Governor needs.

--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

In the classification function we evaluate by login. If the login is Dbauser, the incoming session will be in the Dbagroup workload group. Otherwise, the default workload group is entered. Finally, we register and configure it in the resource Governor so that our settings are in effect.

--Register The classifier Function within Resource Governor
ALTER Resource with
Governor
 FUNCTION = dbo. Myclassifierfunction
 )
go

--Reconfigure Resource Governor
ALTER Resource Governor reconfigure Go

Now when you create a login named Dbauser, you can use it to connect to your SQL Server. You can see at the DMV sys.dm_exec_sessions Whether the session coming under GROUP_ID column validation is in the correct workload group. Next I create a nonclustered index in the DataKey in the Factonlinesales table of the CONTORETAILDW database.

--Create a simple non-clustered index
create nonclustered index idx_datekey on factonlinesales (datekey) Go

We created the pool of resources from the start, and now we have no limitations in our pool of resources. So when we do the index rebuild of the nonclustered index we just created, SQL Server takes up a lot of iops. We can verify the resource pool we just created through the "SQL server:resource pool stats:disk Write io/sec" performance counter in performance monitoring.

ALTER INDEX idx_datekey on factonlinesales REBUILD go

You can see that index rebuilds cost nearly 100 ioPS. What I'm going to do next is limit the Dbapool resource pool to just 50 ioPS:

--Let's change the Resource Pool by lowering the maximum IOPS.
ALTER RESOURCE POOL Dbapool with
(
 max_iops_per_volume = M
 )
go

Now when you perform an index rebuild, it is clear in the performance monitor that there is only an average IOPS in a particular resource pool.

The Disk Write IO throttled/sec performance counter will also tell you the limits of the IOPS of the resource governor.

Using the previous resource Governor, the query itself was helpless and whether it was suppressed. This is also a very important factor in tuning performance. When the resource Governor is enabled, no specific wait type appears in SQL Server. My tests show that once the resource Governor is enabled, there are more pageiolatch_sh/pageiolatch_ex waiting types, that's right. The following 2 images show specific wait type information for the session in which the index is rebuilt-the 1th has no resource governor, and the 2nd resource Governor suppresses ioPS.

As you can see from the 2 diagrams, there are huge differences between the 2 running tests, especially the pageiolatch_ex and sos_scheduler_yield wait types.

From my iops suppression, the resource Governor is a good addition to the existing functionality, which makes the resource governor more mature.

You can try this new feature to solve the IOPS problem.

The above mentioned is the whole content of this article, hope to be helpful to everybody's study.