Shulou(Shulou.com)05/31 Report--

How to use resource governor to suppress your storage in SQL Server 2014? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

In today's article, I want to talk about a very cool upgrade in SQL Server 2014: now you can finally suppress queries according to the IOPS you need! The resource governor (Resource Governor) has been introduced since SQL Server 2008, but there are still limitations: you can only limit CPU time (which is great), and you can limit the amount of memory for queries (from each separate query).

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

This has changed since SQL Server 2014, because you can limit IOPS usage by deploying specified resource pools through the resource governor. When you isolate your DBA operations to a specified resource pool, you can specify the maximum IOPS (including the minimum IOPS) that the resource pool can use. So 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 show this behavior with a very simple example. Suppose you are DBA and are about to perform regular index reconstruction operations, which need to be controlled by the resource governor to control their maximum IOPS utilization. In step 1, we create dedicated resource pools and workload groups for the DBA operation.

-- 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 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 is not limited on the first execution, and here we establish the initial baseline based on the IOPS we need. Next I will create the classification function needed by the resource governor.

-- Create a new ClassifierFunction 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 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 with the resource governor so that our settings take effect.

-- Register the ClassifierFunction 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 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 nonclustered index in DataKey in the FactOnlineSales table of the ContoRetailDW database.

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

We created the resource pool from the beginning, and now there is no limit in our resource pool. So when we now rebuild the nonclustered index we just created, SQL Server will take 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 the performance monitoring.

ALTER INDEX idx_DateKey ON FactOnlineSales REBUILDGO

You can see that IOPS costs nearly 100 to rebuild the index. The next thing I want to do is 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 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 limit number of IOPS for your resource governor.

With the previous resource governor, there is no way for the query itself to be suppressed. This is also a very important factor for performance tuning. When the resource governor is enabled, no specific wait type appears in the SQL Server. My tests show that once the resource governor is enabled, there are more PAGEIOLATCH_SH/PAGEIOLATCH_EX wait types, and that's right. The following two images show the specific wait type information for the session where index reconstruction occurs-the first does not have a resource governor, and the second has a resource governor to suppress IOPS.

There is a big difference between the two running tests, especially in the PAGEIOLATCH_EX and SOS_SCHEDULER_YIELD wait types.

From my point of view of IOPS suppression, the resource governor is a good addition to existing functionality, which makes the resource governor more mature.

You can try to use this new feature to solve IOPS problems.

After reading the above, have you learned how to suppress your storage with the resource governor in SQL Server 2014? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.