STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

How to Limit IO Requests with SQL Server 2014 Resource Governor

Resource Governor was introduced in SQL Server 2008 in order to allow us have better control over our system resource consumption. With Resource Governor, we can divide our users to groups, when each group gets a certain percent of our resources. That way, we can reduce the risk of a crazy query consuming all of the server resources and hurting other processes.

The problem is that up and including SQL Server 2012, we could limit the amount of CPU and memory resources a certain group can consume, but couldn’t do it for IO. With SQL Server 2014, we can finally do that.

Let’s see how it’s done on SQL Server 2014 CTP1:

Configuration

First, let’s use a code section by Tibot Karazi to create a database, a table with data, and a user to be throttled (thanks Tibor):

[code lang=”sql”] /******************************** Create the buffer pool extension *******************************/ EXEC sp_configure ‘max server memory’, 200 RECONFIGURE GO /*************************************** Create Resource Governor needed objects ***************************************/ use master GO IF DB_ID(‘ResourceGovernorDemo’) IS NOT NULL DROP DATABASE ResourceGovernorDemo GO CREATE DATABASE ResourceGovernorDemo ON PRIMARY (NAME = N’ResourceGovernorDemo’, FILENAME = N’C:DemosResourceGovernorDemo.mdf’ , SIZE = 250MB , FILEGROWTH = 50MB ) LOG ON ( NAME = N’ResourceGovernorDemo_log’, FILENAME = N’C:DemosResourceGovernorDemo_log.ldf’ , SIZE = 30MB , FILEGROWTH = 50MB)

ALTER DATABASE ResourceGovernorDemo SET RECOVERY SIMPLE GO use ResourceGovernorDemo GO

–Create tables and populate with data, approx 250 MB each CREATE TABLE DemoTable(col1 INT IDENTITY PRIMARY KEY CLUSTERED, col2 INT, col3 CHAR(100)) GO INSERT INTO DemoTable (col2, col3) SELECT TOP (90000) ROW_NUMBER() OVER(ORDER BY a.OBJECT_ID), ‘hello’ FROM sys.columns AS a CROSS JOIN sys.columns AS b GO 25

–Create login IF SUSER_ID(‘BadUser’) IS NOT NULL DROP LOGIN BadUser GO CREATE LOGIN BadUser WITH PASSWORD = ‘Aloha1’

–Create user USE ResourceGovernorDemo CREATE USER BadUser GO GRANT SELECT ON DemoTable TO BadUser GO

[/code]

Now comes the fun part. We will create a resource pool and disallow it from performing more than 50 IO operations per second (per volume):

[code lang=”sql”]

–Create group and pool CREATE RESOURCE POOL LimitedIOPool WITH (MAX_IOPS_PER_VOLUME = 50); CREATE WORKLOAD GROUP LimitedIOGroup USING LimitedIOPool;

[/code]

And to finish the configuration, we’ll add a resource governor classifier function:

[code lang=”sql”] USE MASTER IF OBJECT_ID (‘dbo.ResourceGovernorClassifier’) IS NOT NULL DROP FUNCTION dbo.ResourceGovernorClassifier; GO

CREATE FUNCTION dbo.ResourceGovernorClassifier () RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @GroupName SYSNAME; IF SUSER_SNAME() = ‘BadUser’ SET @GroupName = ‘LimitedIOGroup’; ELSE SET @GroupName = ‘Default’; RETURN @GroupName; END; GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO [/code]

Execution Tests

Now, from the same window, let’s execute a query:

[code lang=”sql”]

USE ResourceGovernorDemo GO –Clear Cache CHECKPOINT DBCC DROPCLEANBUFFERS

GO USE ResourceGovernorDemo GO IF OBJECT_ID (‘tempdb..#temp’) is not null drop table #temp GO SELECT * INTO #temp FROM DemoTable GO [/code]

On Performance Monitor, the Disk Reads/sec and Disk Writes/sec counters look like this:

Now, let’s open another windows and connect with the “BadUser” login. When executing the same query from the new window, the same Performance Monitor counters now look like this:

Obviously, this query took much longer to execute.

When I presented this to my local user group, the guys noticed that reads are indeed throttled, but writes can pass the limit (50 in our case). It can make sense, since a user session doesn’t directly write to disk but to memory buffers that are later written to disk on checkpoint or by the lazy writer. It’s interesting to see if writes are indeed not throttled, and if so, whether or not that’s the reason.

#storage #resourcegovernor #sqlserver2014 #io

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle