Resource Governor

Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.

In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.

Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Types of Resource Issues

Resource Governor is designed to address the following types of resource issues which are commonly found in a database environment:

• Run-away queries on the server. In this scenario a resource intensive query can take up most or all of the server resources.
• Unpredictable workload execution. In this scenario concurrent applications on the same server have workloads of different size and type. For example, two data warehouse applications or a mix of OLTP and data warehouse applications. These applications are not isolated from each other and the resulting resource contention causes unpredictable workload execution.
• Setting workload priority. In this scenario one workload is allowed to proceed faster than another or is guaranteed to complete if there is resource contention. Resource Governor enables you to assign a relative importance to workloads.

-- Which resource pool is using how much of memory
SELECT pool_id
,NAME
,min_memory_percent
,max_memory_percent
,max_memory_kb / 1024 AS max_memory_in_MB
,used_memory_kb / 1024 AS used_memory_in_MB
,target_memory_kb / 1024 AS target_memory_in_MB
FROM sys.dm_resource_governor_resource_pools

Above DMV shows how the Resource Governor pools are configured and the values of memory currently. In case we have InMemory based databases, we need to understand which databases are configured to resource pools and what memory is currently mapped. This can also be got from the DMVs as shown below:

-- Check DB and pool binding

SELECT d.database_id
,d.NAME AS DbName
,d.resource_pool_id AS PoolId
,p.NAME AS PoolName
,p.min_memory_percent
,p.max_memory_percent
FROM sys.databases d
LEFT OUTER JOIN sys.resource_governor_resource_pools p
ON p.pool_id = d.resource_pool_id

The above shows the mapping of DB’s to resource pools. Do let me know if you have something similar used in your environments? If so, please share the same via comments.