SQL Server 2008: Managing Resources with the Resource Governor (part 1) - Resource Pools

2/22/2011 9:14:02 AM
In some cases, your queries may be running as efficiently as possible; they may just be running slow due to limited resources. For example, you may have a backup job or a long-running report consuming all of the system resources and slowing down your production queries. Luckily, in SQL Server 2008 you can use the Resource Governor to limit these resource-intensive operations so they have minimal impact on your environment.

The Resource Governor was introduced in SQL Server 2008 as an Enterprise Edition feature. You can use the Resource Governor to limit resource consumption of inbound requests and prioritize workloads in order to maintain a more predictable environment. Currently, you can only limit CPU and memory, but not IO. The Resource Governor only applies to the Database Engine, which means you cannot use the Resource Governor to manage external SQL Server processes, such as Analysis Services, Integration Services, and Reporting Services. Also, the Resource Governor is confined to a single SQL Server instance, limiting your ability to manage resources between instances.

The Resource Governor consists of three main components: a resource pool, a workload group, and a classifier function. A resource pool is used to carve up physical system resources available within an instance of SQL Server. A workload group is assigned to a resource pool and used to group similar queries so they can be treated as a single unit. Finally, the classifier function is used to assign an incoming request to the appropriate workload group. Figure 1 shows how an incoming request is routed to the appropriate resource pool. We will use the samples throughout this section to emulate the environment displayed in Figure 1.

Figure 1. Resource Governor workflow

1. Resource Pools

Resource pools are based on the memory and CPU that is available to the SQL Server instance where you are configuring the Resource Governor, not the total resources available to the OS. Resource pools are like mini SQL Server instances inside of an instance. The OS shares memory and CPU between SQL Server instances in the same way a SQL Server instance shares memory and CPU between resource pools.

There are two resource pools created when you install SQL Server 2008 (called internal and default). The internal resource pool is designated for all of the internal Database Engine processing. You cannot modify the internal resource pool, and it cannot be accessed by any workload groups other than the internal workload group. Also, the CPU and memory are never limited in an internal resource pool. The internal resource pool takes whatever it needs at the time, and the rest of the resource pools divide the remaining CPU and memory appropriately. The default resource pool is where all processes run that have not been assigned to a specific resource pool.

You can create a user-defined resource pool and set the Min and Max properties for CPU and memory. You can change the properties for a resource pool on the fly, and the new settings will apply to any new incoming requests.

The Resource Governor does not apply to incoming requests initiated by the dedicated administrator connection (DAC). You should enable the DAC on the SQL Server instances where you are running the Resource Governor for troubleshooting purposes.

The combined minimum totals for CPU and memory cannot exceed 100, while the valid values for the maximum setting for each resource pool are anything greater than the minimum setting and not greater than 100. When you define a minimum setting for a resource pool, you are guaranteeing that resource pool will always have at least the minimum amount of CPU and memory specified, which explains why the total amount cannot exceed 100% of the available resources. The maximum settings for resource pools can overlap, and the available memory will be shared accordingly. The maximum settings are also only enforced during times of CPU and memory pressure. For example, if you define a resource pool with a maximum CPU of 20%, the resource pool may actually use 80% of the CPU if the resources are available. The script in Listing 1 will create the Backup and Reporting resource pools shown in Figure 1.

Example 1. Script to Create the Backup and Reporting Resource Pools and Enable Resource Governor
--Create the Backup pool

--Create the Reporting pool

--Enable Resource Governor

We have set the maximum CPU and memory for the Backup pool to 20% and the Reporting pool to 25%. This means that whenever there is resource pressure, the Backup and Reporting pools will only take an average of 45% of the available resources, leaving 55% for all the other requests in the default resource pool. Remember, the internal resource pool will take all the resources it needs, and the rest is considered available to split among the remaining resource pools. The first time you run the ALTER RESOURCE GOVERNOR RECONFIGURE statement, you are enabling the Resource Governor. To disable the Resource Governor, run the ALTER RESOURCE GOVERNOR DISABLE statement.

Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us