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.




Array

No comments yet... Be the first to leave a reply!