For an instructor lead, in-depth look at learning SQL click below.
The SQL Server Resource Governor is a feature that allows database administrators to manage SQL Server workload and system resource consumption. Resource Governor permits you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
In this post, we’re going to introduce you how to handle workload effectively using Resource Governor. Here we go!
Step 1: Enable the Resource Governor
The first step is to enable Resource Governor in your SQL Server instance, since it is disabled by default. This is done using the ‘ALTER RESOURCE GOVERNOR RECONFIGURE’ command. Below is the required SQL code:
|
1 2 3 |
ALTER RESOURCE GOVERNOR RECONFIGURE; |
Step 2: Create Resource Pools
The next thing that we need to do is create resource pools. These are used to group the workload and to set the limits on the amount of system resources that the workloads can use. You can create a pool using the ‘CREATE RESOURCE POOL’ command:
|
1 2 3 4 5 |
CREATE RESOURCE POOL FastCPUPool WITH (MAX_CPU_PERCENT = 70); CREATE RESOURCE POOL SlowCPUPool WITH (MAX_CPU_PERCENT = 30); ALTER RESOURCE GOVERNOR RECONFIGURE; |
The above script creates two resource pools: FastCPUPool and SlowCPUPool. FastCPUPool can consume up to 70% of CPU while SlowCPUPool can use no more than 30%.
Step 3: Create Workload Groups
Now, we need to classify the SQL Server connections into distinct workload groups. We can do this using the ‘CREATE WORKLOAD GROUP’ statement:
|
1 2 3 4 5 |
CREATE WORKLOAD GROUP FastGroup USING FastCPUPool; CREATE WORKLOAD GROUP SlowGroup USING SlowCPUPool; ALTER RESOURCE GOVERNOR RECONFIGURE; |
Here, we have two workload groups ‘FastGroup’ and ‘SlowGroup’ that are using ‘FastCPUPool’ and ‘SlowCPUPool’ respectively.
Step 4: Create Classifier Function
The classifier function is a function that you define to route incoming connections to the appropriate workload group. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION dbo.rgclassifier() RETURNS sysname WITH SCHEMABINDING AS BEGIN IF (ORIGINAL_LOGIN()= 'FastUser') RETURN 'FastGroup' ELSE RETURN 'SlowGroup' END; |
Step 5: Registering the Classifier Function
Last but not least, you need to register the classifier function with SQL Server Resource Governor using the ‘ALTER RESOURCE GOVERNOR’ command:
|
1 2 3 4 |
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier); ALTER RESOURCE GOVERNOR RECONFIGURE; |
With this setup, a user login by ‘FastUser’ will be routed to FastGroup, while any other connections will be directed to SlowGroup. The Resource Governor then applies the corresponding resource limits as per the group’s resource pool.
These are only basic implementations of SQL Server Resource Governor. Remember that Resource Governor offers a high degree of flexibility and customizability beyond this application.
Conclusion
By properly using Resource Governor, organizations can ensure that their resources are allotted efficiently, preventing runaway queries from overtaking system resources and causing system slowness, and allowing you to reserve resources for necessary tasks.