Resource Governor to Control Physical IO

Hi Friends,

Microsoft introduced so many new features and improvements with SQL Server 2014. These features make the life of DBA easier. In addition to these features, One of the improvement provided in SQL Server 2014 is about Resource Governor. Resource Governor is a well-known feature of the SQL Server world to manage the system resource consumption. This was introduced in SQL Server 2008 to set the limit on resources consumed by specific workload. Before SQL Server 2014, this feature was used to specify limits of the amount of CPU and memory that incoming application requests can use. However SQL Server 2014 onward, we can also specify limits of physical IO.

There might be scenarios where we want to set a limit of resource utilization for a specific workload. so that it will not block or impact badly other workload. In such scenarios we can use the resource governor feature. Such scenarios where you wants to use this feature might be index rebuild or running DBCC CHECKDB commands etc. In today’s post we will have a look on controlling physical IO.

First of all, we have to create a resource pool. In the SQL Server Resource Governor, a resource pool is a subset of the physical resources of a SQL Server instance. By default two resource pools (internal and default) created at the time of SQL Server installation. SQL Server allows us to create a user defined resource pool. With this resource pool we will set the limits of IO resource. Here this limit can be applied using MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME as shown below:

USE [Master]
GO
CREATE RESOURCE POOL Pool_With_Restricted_IO 
WITH
(
       MIN_IOPS_PER_VOLUME = 1,
       MAX_IOPS_PER_VOLUME = 40
);
GO

In above TSQL, we have created a resource pool Pool_With_Restricted_IO with min and max IO limits. If you wants to know more about RG pool then click here. In addition to the pool, we have to create a workload group that will use the resource pool created earlier. By default two workloads (internal and default) created at the time of SQL Server installation and mapped to their respective resource pools (internal and default). To know more about RG workload group, click here.

CREATE WORKLOAD GROUP DBA_Workload 
USING Pool_With_Restricted_IO;
GO

In addition to workload group, we will create a classifier function. A classifier function is a user-defined function (UDF) that returns the name of the pool where the incoming request will be directed. That function will assign specific incoming requests to the workload that we have created earlier. This Classification takes place based on a set of user-written criteria inside the classifier function. Following system functions can be used to get the details of the requests so that can be directed to the specific pool: HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER() or IS_MEMBER(). If you wants to know more about classifier function, click here.

CREATE LOGIN TestIO WITH PASSWORD = '(Test@IO@sql2014#)';
GO 
ALTER SERVER ROLE sysadmin ADD MEMBER TestIO;
GO
CREATE FUNCTION dbo.fn_TestIO_Classifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
       DECLARE @WorkloadGroup SYSNAME
          IF SUSER_NAME() = 'TestIO'
            BEGIN
              SET @WorkloadGroup = 'DBA_Workload'
           END
       ELSE
       BEGIN
              SET @WorkloadGroup = 'default'
       END
RETURN @WorkloadGroup;
END
GO

Now, it’s time to assign the created classifier function to the resource governor. So that resource pool restrictions can be used for all the incoming requests from TestIO user.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_TestIO_Classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

If you want to monitor the IO resource consumption, then try to set up the performance monitor as shown below:

Now login on SQL Server 2014 Instance with TestIO user and open a new query session.

Here, I am using an AdventureWorksDW2014 database on this SQL Server 2014 Instance. So I am going to execute below T-SQL Statements on AdventureWorksDW2014. After the execution you can watch the performance monitor. Using below TSQL statement, I am going to create the same table and with same data as FactInternetSales. If you wants to download the AdventureWorks sample database for SQL Server 2014 then you can click here.

SELECT [ProductKey]
      ,[OrderDateKey]
      ,[DueDateKey]
      ,[ShipDateKey]
      ,[CustomerKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[SalesTerritoryKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[RevisionNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[UnitPriceDiscountPct]
      ,[DiscountAmount]
      ,[ProductStandardCost]
      ,[TotalProductCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Freight]
      ,[CarrierTrackingNumber]
      ,[CustomerPONumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
  INTO [AdventureWorksDW2014].[dbo].[FactInternetSalesCopy]
  FROM [AdventureWorksDW2014].[dbo].[FactInternetSales]
GO

As a result, finally you can see that IO has not been crossed the limit of 40 where time is marked as 21:53:55 because MAX_IOPS_PER_VOLUME was set to 40 for the resource pool. There are some constraint with RG. One of them is, RG is limited to the SQL Server Database Engine. There is no workload monitoring or workload management between SQL Server instances. If you want to see the properties on RG then click here.

Finally, Clean up TSQL statements for our case are:

1- Remove the entry for the classifier function and then disable Resource Governor (RG).
2- Drop the work load group.
3- Drop the resource pool.
4- Reconfigure RG.
5- Disable and drop the login that was created earlier.

USE [Master]
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = null);
ALTER RESOURCE GOVERNOR DISABLE;
GO
DROP WORKLOAD GROUP DBA_Workload;
GO
DROP RESOURCE POOL Pool_With_Restricted_IO;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER LOGIN [TestIO] DISABLE;
GO
DROP LOGIN [TestIO];
GO

If you wants to know more details about RG, then you can click here. If you wants to know more about types of resource issues and RG constraints click here. This is all about the today’s blog post on SQL Server resource Governor to control IO. I hope it will help to simplify some real world issues.

PS: Before using this feature in production, make sure that you have tested this feature properly in non prod environment. Another important point to keep in mind that Resource Governor does not impose limits on a dedicated administrator connection (DAC) because DAC requests run in the internal workload group and resource pool.

If you have some good or bad experience of using this feature then you can share with us as comments.

HAPPY LEARNING!

Thanks & Regards:
Prince Rastogi

Like us on Facebook | Join the fastest growing SQL Server group on Facebook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on Facebook