Data Management

An Introduction to SQL Server Resource Governor

A brief introduction to SQL Server Resource Governor feature.
This article discuss resource pools, workload groups, classifier functions.

SQL Server resource governor is a feature that aims to facilitate resources (CPU , Memory and IO) management. In this post, we will use the resource governor to allocate cpu and memory to different workload groups (Production, Development and Reporting).

Using SQL Server Management studio to access Resource Governor
Using SQL Server Management studio to access Resource Governor

Step 0 : Preparing the environment

In this demo, we will restrict our SQL server instance to use only one CPU and 512 MB. We can do this using SSMS , by accessing the server properties.

We can achieve the same result using T-SQL.

Step 1: Creating resource pools

A resource pool represents the physical resources of the server. Two resource pools (internal and default) are created when SQL Server is installed.

So, let’s create two resource pools, the first one for production and the second one for reporting and development.

Step 2: Creating workload groups

A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool.
In this demo, we’ll create three workload groups, one for production, the second one for reporting and the last one for development.

In the object explorer, we can see the newly created resource pools and workload groups

Next, we’ll see how to assign each session to a specific workload group.

Step 3: Classifying sessions using a classifier function

To help SQL Server assigning each request to a specific workload group, we need a classifier function.
It is simply a user defined function returning the name of the workload group to be used in order to respond to the incoming request.

In this demo, we’ll create three SQL server logins and we’ll be classifying the incoming requests based on the login name.

The function will assign a workload group based on the user running the query against SQL Server.

A final step is to attach the classifier function to the resource governor.

Step 4: Demo

To test our configuration, we will use a query that will keep the CPU busy. We will be executing the same query by the ProductionUser, ReportingUser and DevelopmentUser using a batch.

Nota: In order to be able to run the batch file you should enable SQL Server authentication mode .

Adding Perf counters

Adding CPU usage per pool counters

Starting the batch file

As you expect, each user gets the same amount of CPU, which leads to Production getting less than the secondary pool. Let’s change this beaviour by making sure that the Production pool is getting at least 70% of CPU.

After we made this change, now the Production Pool is getting at least 70% of the existing CPU.

The Production Pool is getting a minimum of 70% of CPU

Leave a Reply