Control IO Resource using SQLserver 2014 Resource Governor

Resource Governor introduced by Microsoft in sqlserver 2008 enterprise edition to control memory and CPU resources usage by specifying limits on consumption by incoming request.  For more information, please read on msdn(https://msdn.microsoft.com/en-us/library/bb933866.aspx).In sqlserver 2014, IO set limit is introduced to control IO usage.

IO set limit is useful when ever you need to restrict some low priority process and allow high priority process utilize all the available resources.

 

Lets quickly jump into demo.

  • Create a test database.

USE [master]

GO

/****** Object:  Database [RG_IO_Test]    Script Date: 3/29/2015 12:09:07 PM ******/

CREATE DATABASE [RG_IO_Test]

CONTAINMENT = NONE

ON  PRIMARY

( NAME = N’RG_IO_Test’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\RG_IO_Test.mdf’ , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N’RG_IO_Test_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\DATA\RG_IO_Test_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

 ALTER DATABASE [RG_IO_Test] SET COMPATIBILITY_LEVEL = 120

GO

  • Create a login and restrict this user with IO.

USE master

GO

/*Create Login assign to RG_IO_test database as default and add user to sysadmin role*/

CREATE LOGIN RG_IO_test_user WITH PASSWORD=’Password01!’,DEFAULT_DATABASE=RG_IO_test

GO

USE RG_IO_Test

GO

CREATE USER RG_IO_test_user FOR LOGIN RG_IO_test_user;

GO

ALTER ROLE db_owner ADD MEMBER RG_IO_test_user

         3)       Configure Resource Governor.

/*Enable Resource Governor*/

–check if resource governor is enabled 0= disbaled 1= enabled

select is_enabled from sys.resource_governor_configuration

— if it returns 0 then run the following command

USE master

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

    4) Create Resource pool.

 — Create a Resource pool by specifying Max and min io disk operations(Reads and writes)

— i have configure 30 is max for (read/write) 5 is min for read and write

USE master;

GO

CREATE RESOURCE POOL RG_IO_test_user_Pool WITH

(

       MAX_IOPS_PER_VOLUME = 30,

       MIN_IOPS_PER_VOLUME = 5

);

GO

    5) Create Resource group to resource pool.

 

— Create a resource group to resource pool

USE master;

GO

CREATE WORKLOAD GROUP RG_IO_test_user_Group

USING RG_IO_test_user_Pool;

GO

6)  Create classifier function that will identify when to use RG_IO_test_user_Group.

Following checks if incoming request from RG_IO_test_user then it set group to restrict IO.

 

— Create Classfier Function

USE MASTER;

GO

CREATE FUNCTION dbo.RG_IO_test_user()

RETURNS SYSNAME WITH SCHEMABINDING

AS

BEGIN

       DECLARE @GroupName SYSNAME

          IF SUSER_NAME() = ‘RG_IO_test_user’

            BEGIN

              SET @GroupName = ‘RG_IO_test_user_Group’

           END

       ELSE

       BEGIN

              SET @GroupName = ‘default’

       END

       RETURN @GroupName;

END

7) Assign Classifier function to resource governor.

USE master;

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RG_IO_test_user);

ALTER RESOURCE GOVERNOR RECONFIGURE;

 

Tests

 

Let’s run dbcc check.

USE master;

GO

dbcc checkdb ([RG_IO_Test])

 

stats information is captured from perfom monitor using the two available counters as shown in the figure.

default reads/sec and restricted user read /sec counters are presented in the bellow picture.

RG_IO_test_user_Pool counter is limit to 30 /sec  with respect to our pool configuration.

resource

 

 

 

 

 

 

 

 

 

 

Lets restrict Max IO to 35 and Min 10.

— Create a Resource pool by specifying Max and min io disk operations(Reads and writes)
— i have configure 60 is max for (read/write) 10 is min for read and write
USE master;
GO
ALTER RESOURCE POOL RG_IO_test_user_Pool WITH
(
MAX_IOPS_PER_VOLUME = 35,
MIN_IOPS_PER_VOLUME = 10
);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Now it clearly shows IO is not exceeding 35.
resource2

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s