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