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