SQL Server 2014 Performance – Resource Governor Enhancements and Buffer Pool Extension

In this second of the three part article series, we will first take a look at Resource Governor Enhancements for Physical IO.  Finally, we will talk about buffer pool extension feature.

Resource Governor Enhancements for Physical IO control

Resource Governor allows us to manage our workloads. This feature was first introduced with SQL Server 2008. It allows us to limit the amount of resources that are available to each database workload from the total resources available to SQL Server instance.

One of the main constraints of Resource Governor from SQL Server 2008 to SQL Server 2012 is that it can only be used to specify limits on the amount of CPU and memory that incoming application requests can use within a resource pool. For example, In SQL Server 2012, we can only use resource pools to define minimum and maximum values for memory and CPU utilization. However, SQL Server 2014 further enhances this feature and introduces tow new settings for Resource Governor that can be used to control the physical I/Os issued for user threads of a given resource pool. These new settings are: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. These settings are minimum and maximum physical I/O operations per second per disk volume for a resource pool. These new settings can help one to predict and physical I/O operations for their most critical workload.

In the following section I will present a quick demo that shows how you can use the new setting (MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME) to control a physical I/Os:

Demo:Resource Governor new physical I/O settings:

By default Resource Governor is turned off. You can enable the Resource Governor by using either SQL Server Management Studio or Transact-SQL. To enable the Resource Governor by using SQL Server Management Studio:

In Object Explorer, recursively expand the Management node down to Resource Governor.
Next, right-click Resource Governor, and then click Enable. Next, to enable Resource Governor using T-SQL, you use ALTER RESOURCE GOVERNOR RECONFIGURE statement as follows:

    ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
   

Next, step is to create a resource pools. The resource pool represents a collection of physical resources of the server. For example, in SQL Server 2014 we can create resource pools to define minimum and maximum values for physical I/O, CPU, and memory utilization. SQL Server 2014 has two predefined resource pools: Internal and Default. The internal pool is used solely by the SQL Server database engine while the default pool is used by all workloads that do not have workload defined to them.

To create a resource pool, use theCREATE RESOURCE POOLstatement. The following T-SQL code creates two resource pools, one for developers, and one for 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

Next we need to workload group. A workload group serves as a container for similar sessions. A workload allows all the monitoring sessions and sets policies for the sessions. Each working group is in a resource pool. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server is installed. Resource Governor also supports user-defined workload groups.

To create a workgroup, use theCREATE WORKLOAD GROUPstatement. The following T-SQL code creates two resource pools, one for developer’s resource pool, and one for administrator’s resource pool:

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

Next we need to create a classification function. The classification process assigns incoming sessions and assigns the session requests and queries to specific workload. You can adapt the logic of classification by writing a user-defined function, called classifier function. Resource Governor also supports a classifier user-defined function for implementing classification rules.

Finally, we will create the classifier function that will achieve the following requirement:

• If the user is a member of Admin Group, he will be redirected to AdminWorkGroup
• If the user is a member of Developer Group, he will be redirected to DeveloperWorkGroup
• Redirect all other user sessions to default group

Here is the code for our classifier function:

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

Finally we register the classifier function with Resource Governor:

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

For more information, refer to Resource Governor .

Buffer pool extension

Thebuffer pool extensionfeature provides each SQL Server node the ability to have its own solid-state drives (SSD) as a non-volatile random access memory for buffering. This is the server-level configuration that allows a buffer pool to accommodate larger OLTP workloads. This helps to resolve I/O bottlenecks while improving overall I/O throughput, due to lower latency and better random I/O performance of SSDs. In addition, it guarantees no risk of data loss, as it only deals with clean pages.

The following is the typical syntax to enable this feature:

       ALTER SERVER CONFIGURATION
 SET BUFFER POOL EXTENSION
 { ON ( FILENAME = 'os_file_path_and_name'
     ,SIZE = <size> [ KB | MB | GB ] )
 | OFF }

The buffer pool extension feature is only supported on 64-bit Enterprise, Developer or Evaluation editions of SQL Server 2014.

]]>