Resource Governor is a powerful feature in SQL Server that allows you to manage workload and system resource consumption. It enables you to limit the amount of CPU and memory consumed by different workloads running on the server, ensuring optimal performance and preventing resource contention.

So why is Resource Governor required? Well, imagine a scenario where you have a server running both OLTP transactions and resource-intensive reports. Ideally, you would have separate servers for each workload, but that’s not always possible. In such cases, Resource Governor comes to the rescue by allowing you to allocate resources to each workload, ensuring that critical transactions are not throttled.

Let’s take a look at a couple of real-world examples where Resource Governor can be very useful:

Scenario 1: You have a server running both OLTP transactions and resource-intensive reports. By limiting the resources allocated to the reporting workload, you can ensure that the critical OLTP transactions are not affected.

Scenario 2: You have two DBAs in your organization. One DBA runs critical queries for the business, while the other DBA performs database maintenance tasks. With Resource Governor, you can allocate resources to each DBA, ensuring that the critical queries are not impacted while allowing the other DBA to perform their tasks.

SQL Server comes with two default resource governor components:

1. Internal: This is used exclusively by the database engine, and users have no control over it.
2. Default: This is used by all workloads that are not assigned to any other group.

The major components of Resource Governor are:

• Resource Pools: These define the amount of CPU and memory resources allocated to a specific workload.
• Workload Groups: These map to resource pools and determine which resources are allocated to each workload.
• Classification: This involves creating a user-defined function that routes the workload to the appropriate workload group based on specified criteria, such as the login name or host name.

Configuring Resource Governor can be done using T-SQL. Here’s an example:

CREATE RESOURCE POOL ReportServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO

CREATE RESOURCE POOL PrimaryServerPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
GO

CREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool ;
GO

CREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool ;
GO

CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportServerGroup'
ELSE IF (SUSER_NAME() = 'PrimaryUser')
SET @WorkloadGroup = 'PrimaryServerGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Resource Governor can be disabled and cleaned up using the following script:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO

ALTER RESOURCE GOVERNOR DISABLE
GO

DROP FUNCTION dbo.UDFClassifier
GO

DROP WORKLOAD GROUP ReportServerGroup
GO

DROP WORKLOAD GROUP PrimaryServerGroup
GO

DROP RESOURCE POOL ReportServerPool
GO

DROP RESOURCE POOL PrimaryServerPool
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Resource Governor is a powerful tool that can greatly enhance the performance and manageability of your SQL Server environment. In future posts, we will dive deeper into this topic and explore more advanced features and use cases.