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).