Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick

Resource Governor Enhancements in SQL Server 2014

Rate me:
Please Sign up or sign in to vote.
4.89/5 (2 votes)
14 Jan 2015CPL3 min read 18.2K   1   8
Resource Governor Enhancements in SQL Server 2014

Introduction

The coming on SQL server 2008 marked the introduction of the Resource Governor, which helps users to manage workloads. The Resource Governor allows the user to put restrictions on the amount of resources which can be used from the SQL Server instance’s total available resources in each database’s workload.

The versions of Resource Governor that we released with SQL Server 2008 to SQL Server 2012 were limited in functionality as they could specify limits for only the CPU and the memory requested by the incoming application within a resource pool. In the latest version, SQL Server 2014, the developers have improved this feature to include a couple of additional functionalities. These settings can now control the minimum and maximum physical I/O operations per second per disk volume for user threads of a given resource pool. These new settings are: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. The benefit of these settings is that users can now predict the physical I/O operations for their most important functions.

While one should hire professionals to handle the servers, it’s a good idea to know the basics. Let’s see how one can use the new to control a physical I/Os:

Resource Governor Physical I/O Settings

The Resource Governor is inactive by default. It can be activated in two ways:

SQL Server Management Studio

Recursively expand the management node in object explorer right down to Resource Governor. Right click on Resource Governor and click Enable.

Transact-SQL

To activate Resource Governor through Transact-SQL or T-SQL, you can use ALTER RESOURCE GOVERNOR RECONFIGURE statement as follows:

SQL
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Now that the Resource Governor has been activated, you need to create the resource pools, a collection of the physical resources of the server. In the latest version, SQL Server 2014, the user can define minimum and maximum values for physical I/O, CPU, and memory utilization in the created resource pools.

However, there are also two predefined resource pools which can be used – Internal & Default. The SQL Server database engine uses the internal pool, while the undefined workloads use the default pool.

Creating a resource pools is quite simple using the CREATE RESOURCE POOL statement. In T-SQL, using the following code will create one resource pool for the developers and one for the administrators.

SQL
USE [master];
GO
CREATE RESOURCE POOL DevloperIOQueries1
WITH
(
MIN_IOPS_PER_VOLUME = 20, MAX_IOPS_PER_VOLUME = 60
);
GO
CREATE RESOURCE POOL AdminIOQueries1
WITH
(
MIN_IOPS_PER_VOLUME = 50, MAX_IOPS_PER_VOLUME = 90
);
GO

Once the resource pool has been created, we need to manage the workload group, which acts as a collection of similar sessions, each serving as a resource pool. Workload Groups for the corresponding internal and default resource pools are created and mapped during the installation of the SQL server, though user-defined workload groups can also be used.

The CREATE WORKLOAD GROUP statement is used to define new workgroups. To create a resource pool for the developer and one for the administrator, we can use the following code:

SQL
USE [master];
GO
CREATE WORKLOAD GROUP DeveloperWorkGroup
USING DevelopersIOQueries;
GO
CREATE WORKLOAD GROUP AdminWorkGroup
USING AdminIOQueries;
GO

The next step is to create a classification function in order to assign incoming sessions and their queries and requests to a particular workload. Of course, a user defined function can also be used to adapt the logic of the classification. This user defined function is called the classifier function. The classifier user defined function is used with the Resource Governor to implement classification rules.

The last step is to create the classifier function that will redirect members of the Admin group, developer group and other user sessions to the Admin Work Group, Developer Work Group and the Default Group, respectively. The code for classifier functions is given as follows:

SQL
USE [master];
GO
CREATE FUNCTION dbo.fnRGClassifier ( )
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup [sysname]
IF ( IS_MEMBER(N'DomainName\DeveloperGroup') = 1 )
SET @WorkloadGroup = N'DeveloperWorkGroup'
ELSE
IF ( IS_MEMBER(N'DomainName\AdminGroup') = 1 )
SET @WorkloadGroup = N'AdminWorkGroup'
ELSE
SET @WorkloadGroup = N'default'
RETURN @WorkloadGroup;
END
GO

To register the classifier function with Resource Governor, the following code is used:

SQL
USE [master];
GO
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fnRGClassifier);
GO

The buffer pool extension feature in SQL Server 2014 gives each server node an ability to have its own SSD drive as a non-volatile random access memory for buffering. However, this feature is only supported on 64-bit Enterprise, Developer or Evaluation editions of SQL Server 2014.

License

This article, along with any associated source code and files, is licensed under The Common Public License Version 1.0 (CPL)



Comments and Discussions

 
QuestionComment. Pin
Walter Moore6-Jul-16 1:36
Walter Moore6-Jul-16 1:36 
GeneralComment Pin
Andrew 1106-Sep-15 22:08
Andrew 1106-Sep-15 22:08 
QuestionComment Pin
Tom Clark17-Feb-15 0:09
Tom Clark17-Feb-15 0:09 
QuestionComment Pin
sujain thomas17-Jan-15 2:03
sujain thomas17-Jan-15 2:03 
QuestionComment Pin
Member 1137391017-Jan-15 1:56
Member 1137391017-Jan-15 1:56 
GeneralMy vote of 5 Pin
Volynsky Alex15-Jan-15 14:10
professionalVolynsky Alex15-Jan-15 14:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.