16.3. Workload groups

Classifying incoming database connections into a workload group offers a number of benefits, including the following:

• Connections that share a similar property, for example, Application Name, can be grouped together for purposes of applying resource usage boundaries via a specific resource pool.

• Application of resource usage constraints, such as a custom MAXDOP setting, can be made at a workload group level, thereby enabling more control over resources in mixed-purpose environments.

• Resource usage can be monitored at a group level, enabling a deeper understanding and visibility of current, aggregate, minimum, and maximum resource usage for a given group.

As covered earlier, there are two preexisting system workload groups, default and internal. The default group is used for any connections not classified into a user-defined group or classified into a group that no longer exists. The internal group, used for internal SQL Server operations, can be monitored, but connections cannot be classified into this group, nor can the group be modified in any way.

In addition to these system workload groups, user-defined groups can be created using the CREATE WORKLOAD GROUP T-SQL command. To gain a deeper understanding of workload groups, let's examine the syntax of this command before discussing its optional arguments:

CREATE WORKLOAD GROUP group_name
[ WITH
    ( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
           [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
           [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
           [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
           [ [ , ] MAX_DOP = value ]
           [ [ , ] GROUP_MAX_REQUESTS = value ] )
 ]
[ USING { pool_name | "default" } ]

• IMPORTANCE—When multiple workload groups are set to use the same resource pool, the Importance argument enables tasks from one group to be weighted ahead of others; for example, if a Miscellaneous Query resource pool is used by two workload groups called AdHoc and Admin, assigning a high importance to the Admin group will place its access to resources before that of the AdHoc group. A high importance should not be confused with priority access to system resources; that is, importance is a simple weighting mechanism to establish order among multiple groups in the same resource pool.

• REQUEST_MAX_MEMORY_GRANT_PERCENT—This argument enables the specification of the largest allowable percentage of resource pool memory that can be assigned to a single request from the group; for example, a value of 10 would permit a maximum of 50MB of memory to be assigned to a query from a pool with a 500MB memory size. A query that exceeds this value will be met with an error message similar to that shown in figure 16.4.

• REQUEST_MAX_CPU_TIME_SEC—Similar to the MAX_MEMORY_GRANT argument, this argument applies to the maximum CPU seconds; however, rather than cancel the query, Resource Governor will allow the query to continue and will generate an alert. We'll examine Resource Governor alerts and monitoring in more detail later in this chapter.

• REQUEST_MEMORY_GRANT_TIMEOUT_SEC—This argument represents the maximum amount of time in seconds that a query from the group will wait for a memory grant. After the time period expires, rather than the query failing, it will receive the minimum memory grant, which may result in lower-than-expected performance.

• MAX_DOP—A workload group can be configured with its own default MAXDOP level. Doing so allows commands from a group to use a MAXDOP setting that may differ from the server default, without specifying an explicit MAXDOP setting. If a query from the group is executed that does specify a MAXDOP value, it will be used, so long as the value does not exceed the group's MAXDOP setting. This argument presents some interesting possibilities; for example, Microsoft Report Builder provides no option to specify a MAXDOP value to assign to the queries it generates. Thus, the only option is to use a server default MAXDOP setting to control the parallelism of its queries; however, changing the server default may introduce unwanted results in other areas. Using Resource Governor, we can classify Report Builder queries into a workload group with its own MAXDOP setting.

• GROUP_MAX_REQUESTS—This argument allows a limit to be applied to the number of simultaneous requests that can execute from the workload group. However, in some cases, the SQL engine may allow this limit to be exceeded if doing so prevents a blocking or deadlock scenario from occurring.

• USING—This argument links a workload group to a resource pool. If this argument is excluded, the default pool is used.

As with classifier functions, you should be careful when creating workload groups. The group and pool names are returned in any error messages, potentially exposing information that could be used maliciously. Figure 16.4 contains an example of an error message that includes the group/pool names.

Figure 16.4. Carefully consider the names assigned to workload groups and resource pools because the names are returned in error messages, as shown in this example.
 

Finally, a user connection classified into a workload group remains in the group for the life of the connection, regardless of whether the classifier function is changed while the connection is active.

As you've just seen, one of the major roles for a workload group is to define a connection's resource pool.