SQL Server Governor: The unknown hero

http://www.codeproject.com/Articles/434467/SQL-Server-Governor-The-unknown-hero

Table of contents

• Introduction
• What is the use of SQL Server Governor?
• How do we configure SQL Server Governor?
• See it working
• Summarizing how the Governor works

Introduction

SQL Server is a massive RDBMS software. Even after so many years of using it, I always find something new in it. I wonder how many hidden gems still exist unexplored inside this huge giant.

The sad part is some of those features are only known to Microsoft (naturally, they created it, so..) and are unknown to most developers.

Recently I came across a scenario where I needed a quick solution. I had some heavy running queries and I wanted to specify a limit on the amount of resources they consume. These queries where eating up all the memory allocated to the SQL Server process and affecting other queries.

I was one of those lucky people to get introduced to this lovely unsung and quiet hero: SQL Server governor, which helped me resolve my issues. 

The strange thing is, it existed from 2008 and I never knew about it. So via this article I want to make a small effort of introducing this feature so that it does not die silently due to less usage. We all know Microsoft is known for making features obsolete (even if they are useful) if it is not used by many people. 

You can also see check out my site where I have written a huge number of SQL Server interview questions:

What is the use of SQL Server Governor?

SQL Server is a giant processing engine which processes various kinds of workloads like SQL queries, transactions, etc. In order to process these workloads, the appropriate CPU power and RAM memory needs to be allocated.

Now workloads are of different nature: some are light workloads while some are heavy. You would never like heavy SQL operations hijacking the complete CPU and memory resources thus affecting other operations.

One way to achieve this is by identifying these SQL queries and putting a restriction on the maximum CPU and memory resource for these queries. So for example, as shown in the below figure, if you have some heavy SQL which does reporting, you would like to allocate to it 80% of the CPU and memory resources. While for lightweight SQL, you would like to allocate only 20%.

This is achieved by using SQL Server Governor.

How do we configure SQL Server Governor?

Configuring SQL Server governor is a four step process:

Step 1: Create a pool and provide the CPU and memory limitation.

So to create a resource pool, browse to the management folder, right click on Resource Governor, and click on “New resource pool”.

You can then create a resource pool like the one we have created in the below figure: “OurPool”.

Step 2: Specify the workload.

The next step is to specify the workload for that resource pool. Workloads are partitions which divide the pool into logical pieces to run your query load. For instance let’s say in this pool “OurPool” if you want to run SQL Management Studio and Reporting Services. You can create two workload partitions: one for Management Studio and the other for Reporting Services, see the previous image. You can also see how we have allocated 25% of the memory for Reporting Services and 75% for Management Studio.

So now whenever Management Studio or Reporting Services applications run, they will be allocated resources from this pool with a resource constraint as specified in the above image.

Step 3: Create a user defined function.

So now that we have created pools and workloads, the final step is to map the incoming SQL request to the appropriate workload. This is done by using a user defined function which is termed as “classifier functions”.

So go to the master database and create a function as shown below. You can see how the user defined function checks for the app name and assigns it to the workload group.

Collapse | Copy Code

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
create FUNCTION [dbo].[Class_funct]() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;
 
  IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
      SET @workload_group = 'Managementstudio';
  IF (APP_NAME() LIKE '%REPORT%')
      SET @workload_group = 'Reporting';
     
  RETURN @workload_group;
END;

Step 4: Assign the function to the pool.

The final step is to assign the user defined function to the pool. Edit the pool and select the function from the dropdown. Do not forget to enable the resource governor.

See it working

One you have done the configuring part, let's test if this really works. So let’s go and run Performance Monitor. Click on Start, run and type “perfmon”, and press Enter.

Go to the counters; in counters, go to SQL Server resource pool stats. Select all the pool instances and add used memory counters from it.

Now go and run your SQL Server Management Studio and see how the “custompool” memory increases.

Note: You can see that there are two extra pools: “default” and “internal”. The default pool is used for all SQL Server activities. The internal pool is used exclusively for internal requests which come from the SQL server itself.

Summarizing how the Governor works

1. When a request comes to SQL Server, it first checks if this is an internal request or a normal end user request.
2. If it’s an internal request, it goes straight to the internal pool for resource allocation.
3. If it’s an external request, the classifier function comes into action and checks what kind of workload it is, and accordingly assigns it to the pool (see the user defined function shown in the previous section of this article). 

Also via this article I would like to share this nice SQL Server training video “How indexes improve select query performance”: