16.8.2. Workload Groups
The Resource Governor uses
workload groups as a way to segregate all incoming requests that have
predefined common characteristics together as a single unit. Workload
groups allow you to define and monitor specific attributes for each
individual workload group. You can also dynamically move workload groups between resource
groups if you determine they would be better suited for a different
resource pool based on your monitoring statistics. Workload groups
consist of two predefined groups that map to the corresponding resource
pool: internal and default. The same rules apply to the internal and
default workload groups that we discussed for the internal and default
resource pools. You can set the following properties when creating a
workload group:
IMPORTANCE:
The weight of importance a workload group is given within a resource
pool. Workload groups with different levels of importance do not affect
each other outside of each resource pool. Valid values are LOW, MEDIUM, and HIGH with the default being MEDIUM.
REQUEST_MAX_MEMORY_GRANT_PERCENT:
The maximum percentage of memory that a single request is allowed to
consume from a given resource pool. The valid range is from 0 to 100
with a default setting of 25.
REQUEST_MAX_CPU_TIME_SEC: The maximum amount of time (in seconds) that a request will run before generating the CPU Threshold Exceeded
event. Resource Governor will not prevent the query from running; it
will just raise the event so you can capture requests that exceed your
defined threshold. Valid values are 0 or greater with a default setting
of 0, which is unlimited.
REQUEST_MEMORY_GRANT_TIMEOUT_SEC:
The maximum amount of time a query will wait (in seconds) for a memory
grant to become available. Valid values are 0 or greater with a default
setting of 0, which means an internal calculation based on the query
cost will be used to determine the maximum time.
MAX_DOP: The maximum degree of parallelism for the workload group. The default is 0, which uses the global SQL Server setting for MAX_DOP.
GROUP_MAX_REQUESTS:
Maximum number of simultaneous requests the workload group can execute
at any given time. Valid values are 0 or greater with a default setting
of 0, which is unlimited.
Let's create the workload groups shown in Figure 16-15 and assign them to their respective resource pools. The script in Listing 2 will create the BackupGroup, ReportingGroup, and the TestingGroup and reconfigure the Resource Governor so they will be immediately active and awaiting processes.
Example 2. Script to Create the Backup, Reporting, and Testing Workload Groups
CREATE WORKLOAD GROUP BackupGroup
USING BackupPool
GO
CREATE WORKLOAD GROUP ReportingGroup
WITH
(IMPORTANCE = MEDIUM,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 30,
REQUEST_MAX_CPU_TIME_SEC = 0,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
MAX_DOP = 0,
GROUP_MAX_REQUESTS = 0)
USING ReportingPool
GO
CREATE WORKLOAD GROUP TestingGroup
WITH
(IMPORTANCE = LOW,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 20,
REQUEST_MAX_CPU_TIME_SEC = 180,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
MAX_DOP = 1,
GROUP_MAX_REQUESTS = 20)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
|
If you look at the BackupGroup,
you will see that all of the parameters are optional; you are not even
required to specify the resource pool. If you do not manually assign a
workload group to use a specific resource pool, it will automatically be
assigned to the default resource pool. If you look at the TestingGroup,
you can see that we did not assign it to a specific resource pool,
since we want to create it in the default resource pool. We have
throttled back the TestingGroup quite a
bit, giving it low priority, only 20% of the resource pool for a single
request, only one processor, and allowing it to run only 20 processes at
any given time. We have also configured the TestingGroup
to raise an event every time it executes a query that takes longer than
180 seconds. At this point, your workload groups are configured and
waiting for incoming processes. Your current configuration in SQL Server
Management Studio should mirror the configuration shown in Figure 2. The next step is to configure the classifier function to send the processes to the appropriate workload groups.
