In SQL Server 2014, how does one use resource controllers to suppress your storage ?, Sql2014

In SQL Server 2014, how does one use resource controllers to suppress your storage ?, Sql2014

In today's article, I want to talk about the cool improvement in SQL Server 2014: Now you can suppress queries based on the required IOPS! Resource Governor has been introduced since SQL Server 2008, but the features provided are still limited: You can only limit the CPU time (this is already great ), you can also limit the amount of memory for queries (from each independent query.

However, as a DBA, you often perform database maintenance operations, such as index reconstruction and dbcc checkdb operations. We all know that these operations will bring a lot of IOPS to your storage until the peak value. If you use a 7*24 online database, this will affect your productivity and greatly affect your business and sales.

This has changed since SQL Server 2014, because you can use resource controllers to deploy specified resource pools to limit IOPS usage. When you isolate your DBA from the specified resource pool, you can specify the maximum IOPS (including the minimum IOPS) that the resource pool can use ). Therefore, you can suppress the IOPS required for DBA operations. Your production workload can make better use of your storage. For more information, see Microsoft online help.

I want to use a very simple example to show this behavior. Assume that you are a DBA and you are about to rebuild the regular index. You need to control the maximum IOPS usage by using the resource controller. Step 2: create a dedicated resource pool and workload group for 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 DBAsCREATE WORKLOAD GROUP DbaGroupUSING DbaPoolGO

As you can see from the code above,CREATE RESOURCE POOLThe statement is now available for youMAX_IOPS_PER_VOLUMEAttributes (includingMIN_IOPS_PER_VOLUME). Here I set a high value, so the IOPS will not be restricted during the first execution. Here we set up the initial baseline based on the required IOPS. In the next step, I will create the category function required by the Resource Controller.

-- Create a new Classifier Function for Resource GovernorCREATE FUNCTION dbo.MyClassifierFunction()RETURNS SYSNAME WITH SCHEMABINDINGASBEGINDECLARE @GroupName SYSNAME IF SUSER_NAME() = 'DbaUser'BEGINSET @GroupName = 'DbaGroup'ENDELSEBEGINSET @GroupName = 'Default'END RETURN @GroupName;ENDGO

In the classification function, we perform evaluation based on logon. If you log on to DbaUser, the session you enter will be in the DbaGroup workload group. Otherwise, enter the default workload group. Finally, register and configure the Resource Controller so that our settings take effect.

-- Register the Classifier Function within Resource GovernorALTER RESOURCE GOVERNOR WITH( CLASSIFIER_FUNCTION = dbo.MyClassifierFunction )GO

-- Reconfigure Resource GovernorALTER RESOURCE GOVERNOR RECONFIGUREGO

Now, when you create a login named DbaUser, you can use it to connect to your SQL Server. You canSys. dm_exec_sessionsCheck the group_id column to verify whether the session is in the correct workload group. Next, I will create a non-clustered index in the DataKey IN THE FactOnlineSales TABLE OF THE ContoRetailDW database.

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

We have created a resource pool from the beginning, and there is no limit in our resource pool. Therefore, when we recreate the non-clustered index We just created, SQL Server occupies a large amount of IOPS. We can use the"SQL Server: Resource Pool Stats: Disk Write IO/Sec"Performance counters to verify the resource pool you just created.

ALTER INDEX idx_DateKey ON FactOnlineSales REBUILDGO

We can see that index reconstruction costs nearly 100 of IOPS. Next, I want to limit the DbaPool resource pool 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

Now, when you re-create an index, you can clearly see in the performance monitor that there is only an average of 50 IOPS in a specific resource pool.

In additionDisk Write IO Throttled/secThe performance counter will also tell you the maximum number of IOPS for resource controllers.

Using the previous resource controller, the query itself has no way to determine whether it has been squashed. This is also an important factor for performance tuning. When resource governor is enabled, no specific wait type appears in SQL Server. My test shows that when Resource Controller is enabled, morePAGEIOLATCH_SH/PAGEIOLATCH_EXThe waiting type. The following two images show the specific wait type information for the session where the index is rebuilt-1st no resource controllers, and 2nd resource controllers suppress IOPS.

From the two figures, we can see that there is a huge difference between the two running tests, especially inPAGEIOLATCH_EXAndSOS_SCHEDULER_YIELDWait type.

From the perspective of IOPS suppression, resource Governor is a good addition for existing functions, which makes resource governor more mature.

You can try this new function to solve IOPS problems.

The above is all the content of this article, and I hope it will help you learn it.