In this Essential Guide to SQL Server 2014 series I’ve been covering some of the most important new features in SQL Server 2014. In the last column I discussed SQL Server 2014’s Buffer Pool Extensions which enable you to expand SQL Server’s Buffer Pool using SSD storage. In this column I’m going to explore the enhancements to another important SQL Server 2014 performance feature: the Resource Governor.
The Resource governor was first introduced back in SQL Server 2008 and its purpose is to make application response time more predictable – especially in multi-tenant environments. The original implementation of the Resource Governor enabled you to limit the CPU and memory that a database request could consume thereby preventing resource intensive queries from consuming too many system resources. This can be particularly important as tools like Reporting Services, Report Builder and Power BI enable end users to create and run their own reports which might be less than optimal from a system resource standpoint.
SQL Server 2014 extends the Resource Governor with the ability to specify minimum and maximum IOPS per volume in addition to controlling CPU and memory. This can be useful in hosting and other database consolidation scenarios where you need to achieve higher database density and more predictable application performance. If one of the applications has a very IO intensive workload that workload can potentially overload the storage subsystem impacting other applications. IO demands can impact other workloads just as much or more as memory and CPU demands.
The Resource Governor is composed of three main components: resource pools, workload groups, and a classifier.
Resource Pools – By default the Resource Governor uses two pools: the Internal Pool and the Default Pool. The Internal Pool is used by SQL Server itself and all user activity takes place in the Default Pool. You can also create your own custom defined resource pools.
Workload Groups – Workload groups represent the jobs running on the system. By default there are two workload groups: Internal Workgroup and the Default Group. All internal system activity is grouped into the Internal Workgroup while all user activity is grouped into the Default Group. You can also create your own custom workload groups. There are three different workload group priorities: low, medium and high.
Classifier – The Classifier is a function that evaluates incoming job requests and categorizes them into the appropriate workload group.
SQL Server 2014’s Resource Governor adds two new resource pool options that allow you to control the I/O usage: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. To take advantage of these new settings you would need to create a resource pool, workload group and a classifier function that will map the incoming connections to the corresponding resource pool. For example, the following T-SQL code shows how you can create a resource pool, workload group and classifier function that limits the IO for the specified database to 50 IOPS.
CREATE RESOURCE POOL MyDBPool WITH
MAX_IOPS_PER_VOLUME = 50,
MIN_IOPS_PER_VOLUME = 1
CREATE WORKLOAD GROUP MyDBGroup
CREATE FUNCTION dbo.MyDBFunction()
RETURNS SYSNAME WITH SCHEMABINDING
IF ORIGINAL_DB_NAME() = 'MyDB'
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.MyDBFunction);
ALTER RESOURCE GOVERNOR RECONFIGURE;