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.
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 RESOURCE POOL BackupPool
WITH
( MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 20,
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 20)
GO
--Create the Reporting pool
CREATE RESOURCE POOL ReportingPool
WITH
( MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 25,
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 25)
GO
--Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
|
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.
