Resource Governor in SQL Server 2008
Posted by decipherinfosys on April 12, 2009
When we were working with the CTP of SQL Server 2008 in early 2007, we had blogged about the Resource Governor feature that was going to be introduced in SQL Server 2008. That was more of a wish list based on our experience with this feature in Oracle. This feature has existed in Oracle since quite some time so we are happy to note that we have similar capabilities in SQL Server as well now. In a nutshell, this feature allows us to control how much CPU and memory resources are assigned to a particular workload. So, if you have a production system on which the transactional system applications are run, reporting is done of the same database and adhoc queries are being fired off on the same database as well, you can now control and manage your CPU and memory resources better by establishing a designated resource allocation. By providing this feature, DBAs can now manage unpredictable workloads in an easy manner, can also set limits on bad queries (could be done in prior versions in a different way – see here and here) and put priority – either at the user level so that some users have a higher priority than others or at the workload level to ensure that the critical activities are not devoid of the CPU/Memory resources.
Before we start going into some of the details, please do note that this feature is available only in the Enterprise Edition and also that it applies only to the database engine i.e. it does not work with SSAS, SSRS and SSIS.
Let’s pick up a common scenario which almost every OLTP application faces. Say you have an OLTP application on which heavy reporting load is also going to be present in the production environment. A common way to tackle this problem is to offload this reporting load to another database on another instance by using replication/database mirroring to keep that database in sync. or use snapshot database feature to create a read only copy of the database on the same instance over which the reporting is done instead of the production database – this still places a load on the instance though. Let’s assume that in a client situation, separating out the reporting load is not feasible and one is forced to use the same database on the instance for both the critical OLTP transactions as well as for all the heavy reporting load as well which is of course critical but not as critical as the OLTP transactions. In such a scenario, one can create workload groups and restrict the resources used by the reporting application to a certain percentage.
So, how can this be achieved? How is the Resource Governor able to distinguish between the different workloads? That is done by using a classification function that is created using T-SQL. This classification function uses the niladic function: APP_NAME() in order to be able to identify the application that is connecting the instance and the database. So, suppose we want to segregate the load between OLTP workload group and a reporting workload group, we would create these workload groups which will serve as a group to contain the connection requests coming from specific applications. So, say we have 1000 connections, 800 of those might fall in the OLTP workload group and 200 might fall in the reporting workload group. At these workload groups level, we then have resource pools which represent the CPU/Memory resources on the server.
One key thing to note is that the resource governor helps in the event of resource contentions only. So, if we have allocated say the OLTP workload group to make use of 100% of the resources and the Reporting Workload group is restricted to say 10% of the resources, then if the OLTP workload is less at a given time and the Reporting workload is high and needs more than the allocated 10% of the resources, it will be able to use them. However, if the workload is high and there is contention on the resources that are available, then the Reporting workload group will not be able to use more than 10% of the resources and will have to wait until resources become available to it.
Let’s see how we can go about configuring this. Let’s create the resource pools first:
/*Resource Pool for the OLTP Workload*/
CREATE RESOURCE POOL RP_OLTP
/*Resource Pool for the Reporting Workload*/
CREATE RESOURCE POOL RP_RPT
What we have done here is that we have created two resource pools – one for the OLTP workload and the other one for the Reporting workload and have assigned the MIN and MAX values for the CPU and Memory resources. Now, the next step is to create the workload groups and specify which one of these resource pools can they use:
/*Workload Group to hold all the connections coming from the OLTP Application
These will use the RP_OLTP resource pool*/
CREATE WORKLOAD GROUP WG_OLTP
/*Workload Group to hold all the connections coming from the Reporting Application
These will use the RP_RPT resource pool*/
CREATE WORKLOAD GROUP WG_RPT
So, now we have the resource pools created and we also have the workload groups created. The next thing to do is to create the classification UDF which will be used to take the connections and put them in the respective workload groups which have those resource restrictions placed upon them as shown above. Per instance, only one classification function is allowed so all assignments for the incoming connections need to be made there. The connections that are not explicitly assigned to a specific workload group, those are assigned to the default workload group. This classification function executes post the login authentication and post the firing off any logon triggers. BOL specifics certain considerations for creating the classifier function – you can read more on it here and here. It also lists out the system functions that can be used within the scalar classification UDF – one such function that we had mentioned above was APP_NAME(), there are others as well like HOST_NAME(), SUSER_NAME() etc.
So, let’s create a simple classification UDF for illustration purposes:
CREATE FUNCTION dbo.WG_Classify()
DECLARE @wg SYSNAME
SELECT @wg = CASE APP_NAME()
WHEN ‘Report Server’ THEN ‘WG_RPT’
In this function, we have segregated the load into 2 sections – one for the Reporting Workgroup load and all the rest of them falling into the OLTP workgroup load. So, we now have:
a) The resource pools created,
b) The workgroups created which are going to be using those resource pools, and
c) The classification functions for classifying the incoming connections and assigning them to the respective workgroups.
So, now what? Are we done? Not yet – in order to enable the resource governor, we now have to use the “ALTER RESOURCE GOVERNOR” command to first register our classification UDF with the resource governor and then have to start the resource governor by using the “ALTER RESOURCE GOVERNOR RECONFIGURE” command.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.WG_Classify)
ALTER RESOURCE GOVERNOR RECONFIGURE
After executing these commands, we have now created the rules so that the resource pool restrictions can now be placed on the incoming connections.
This was just a very basic introduction to Resource Governor … it is very important to understand the inner workings of this wonderful feature as well as to understand how to go about troubleshooting issues related to it’s configuration. Here are some excellent articles/posts that have helped us in this regard:
- Resource Governor Questions – from CSS SQL Server Engineers – here.
- MSN and YouTube videos showing the usage of this feature – here. And a technet video – here.
- BOL coverage on Resource Governor – here.
- Resource governor article by Muhammad Siddiqi – here.
- Ross Mistry’s book – an excerpt is available here as well.
- If you have SQL Server Magazine subscription, it has an excellent article on this topic by MVP Greg Low.