The Resource Governor is a feature of SQL Server that can manage how much system resources different loads are allowed to use and how. It is possible to limit/monitor/prioritize the amount of CPU and memory that a specific load can use on an instance.
The types of queries that run on SQL Server use the resources available on the server and sometimes there can be resource conflicts that affect performance. A common scenario is when an instance has an OLTP load and there is also reporting running against it. Reporting queries are often much more complicated and have longer execution times than the normal OLTP load. An example might be an online shop with a predominantly OLTP load against which the finance and purchasing departments want to run reports and summaries of product sales etc. The optimal solution would have been to run these loads on a different server where the database is synchronised against the OLTP server. However, it is not always possible to use such a solution for cost reasons, for example, and then Resource Governor can be used to limit the resource usage of the reporting part. This reduces the risk of the more critical OLTP load being affected by the report part.
The process of setting up the three main components of the Resource Governor, Resource Pools, Workload Groups and Classification consists of a few simple steps:
- Create a Resource pool
- Create Workload group
- Create a Classification function according to the criteria needed
- Activate the Resource Governor with the Classification function
The goal is for different types of loads to have different resource pools assigned to the resources that should be available for that particular load. The Workload group contains the loads that will use the resources in the Resource pool and the Classification function is a user defined function that defines which loads will belong to which Workload group. This can be done by e.g. checking which login is running the query, which server it comes from, which application it comes from etc.
I hope this introduction has given you some insight into what the Resource Governor can do. In the next post I will show a simple example of how to set it up.