Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DB NewsFeed

Matan Yungman is a SQL Server DBA at the Microsoft Protection Services operations team. He fell in love with T-SQL in 2003 and since then took part in various challenging projects, focusing mainly on performance tuning, database development, high availability and database design. He is passionate about SQL Server, databases and technology in general.

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):

/********************************
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:\Demos\ResourceGovernorDemo.mdf' , SIZE = 250MB , FILEGROWTH = 50MB )
LOG ON
( NAME = N'ResourceGovernorDemo_log', FILENAME = N'C:\Demos\ResourceGovernorDemo_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

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):


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

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

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

Execution Tests

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


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

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

RegularLoad

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:

ThrottledLoad2

During Read Activity

ThrottledLoad1

During Write Activity

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.

Comments

Leave a comment on the original post [www.dbnewsfeed.com, opens in a new window]

Loading comments...