Looking into Resource Governor in SQL Server 2008

In this post I would like to talk about a new feature in SQL Server 2008, Resource Governor and what this new feature offers us in terms of performance and good use of resources.

I know there are samples of how to use Resource Governor in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.

Before we move on I would like to note that this is an Enterprise Feature only. In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it from here . This is a trial version. Developer edition has the same features as the Enterprise edition but has different licensing requirements.It is not free as many people believe. Resource Governor works with the database engine and not with Integration,Reporting,Analysis services.

In many cases we have a situation where we have a database, an OLTP system that has heavy workloads.At the same time we have some senior managers that run reports on the same database which makes things even worse when it comes to performance of the OLTP system.Sometimes people find some compromise. The DBAs of such a system tell the senior managers or sales executives to run their long-running reports at times where there is no heavy workload , e.g at late afternoon. Obviously this is not a good solution.It is not a technical solution.With Resource Governor we can control resources (CPU and memory) and assign them to a particular workload.For example we can say that 90% of available resources can be allocated/assigned to OLTP workloads and 10% to be allocated to long-running reports.Well, with that configuration we will impact the performance of the long-running queries but will not affect the OLTP production system.

So we have the typical ( not correct ) configuration where we have the OLTP system(e-shop,ERP) and a third party reporting application that accesses directlyour database.We do not want to have this sort of OLAP reporting blocking the OLTP activity.We want to limit the CPU and memory resources for the reporting application to 10% of what the server has available.Obviously we need some sort of way to divide the various workloads and allocate them memory and CPU. We will do that by using the classification function.The classification function is used by the Resource Governor to determine which connection(application) is made to the database. Basically it tells the Resource Governor which application (OLTP application or Reporting application) is connecting to the database.Then the connection is directed to a particular workload group.The workload group has connections that are defined by the classification function . In our case we need to create 2 workload groups.Now that we have seperated the connections from each application we need to allocate resources to these connections.We will do that by creating resource pools. A resource pool represents the physical resources of a server.We need to create 2 resource pools in our scenario.We will use these resource pools to allocate the minimum and maximum resources (CPU & Memory) that will be allocated to the 2 applications accessing our database.One resource pool is associated with the (OLTP) workload group and the other one is associated to the reporting workload group.

Let’s move on with our hands on example.

1) Launch SSMS and connect to the local instance of SQL Server.We will create the resource pools first.Execute the code below.

USE MASTER
GO

CREATE RESOURCE POOL OLTPApplicationpool
WITH
(
MAX_CPU_PERCENT=100,
MAX_MEMORY_PERCENT=100,
MIN_CPU_PERCENT=90,
MIN_MEMORY_PERCENT=90
);
GO

CREATE RESOURCE POOL ReportingApplicationpool
WITH
(
MAX_CPU_PERCENT=10,
MAX_MEMORY_PERCENT=10,
MIN_CPU_PERCENT=0,
MIN_MEMORY_PERCENT=0
);
GO

By running the code above we say that the OLTP application (through the resource pool) will use up to the 100% of the available CPU and memory resources.At the same time I specify that the OLTP application will use minimum of 90% of the total CPU and resources available.

2) Then we need to create the workload groups. We also need to associate the workload groups to the resource pools.Execute the code below.

CREATE WORKLOAD GROUP workgroupOLTP
USING OLTPApplicationpool
GO

CREATE WORKLOAD GROUP WorkgroupReports
USING ReportingApplicationpool
GO

3) Now we need to create the classification function that will determine which connections will go to which workload group.Execute the code below to create the classification function.

CREATE FUNCTION classificationOLTPReports() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @group_Name  AS SYSNAME
IF (APP_NAME() = 'ABCReports')
SET @group_Name = 'WorkgroupReports'
ELSE
SET @group_Name = 'workgroupOLTP'
RETURN @group_Name
END
GO

4) Now we need to enable the Resource Governor.We will register the classification function first with the Resource Governor.Execute the code below.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.classificationOLTPReports);
GO

-- Start/Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO;

I am sure you can appreciate now the benefits of Resource Governor.Use Resource Governor to make the impact of long-running reports to your heavily accessed database minimal.It is a very good reason to upgrade to SQL Server 2008.

Hope it helps!!!!