1136
Part VIII: Performance Tuning and Optimization
Workload Group
To keep the assignment of resource pools manageable and to allow for highly granular
monitoring and juggling of workloads within a resource pool, SQL Server has workload
groups. You can assign users to a workload group, and assign one or more Workload Group
to a resource pool. This mechanism is loosely analogous to login groups that contain
users.There are two built-in workload groups:■ (^) Internal: Assigned to the internal resource pool. Used by Database Engine.
■ Default: All those sessions that do not classify to any other workload group are
assigned to it.
You can easily create workload groups with T-SQL. The following statement creates a work-
load group named Workload2 and assigns it to CustomPool2.
CREATE WORKLOAD GROUP Workload2
USING CustomPool2
You can also use ALTER and DROP statements to change and delete workload groups.
Following are several examples that show you how to use ALTER statements to do useful
things.
When SQL Server is fi rst installed, the Resource Governor is disabled. The following exam-
ple starts the Resource Governor. After the statement executes, the Resource Governor runs
and can use the predefi ned workload groups and resource pools.
ALTER RESOURCE GOVERNOR RECONFIGURE;
The following example assigns all new sessions to the default workload group by removing
any existing classifi er function from the Resource Governor confi guration. The classifi er
function is a user-defi ned function that uses properties such as IP address, Application
Name, or User Name to assign a session to a workload group. Classifi er functions will be
explained in more detail in the next section.
If you do not designate a specifi c classifi er function, all new sessions assign to the default
workload group. This change applies to new sessions only. This does not affect existing
sessions.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
The following example creates a classifi er function named dbo.rgclassifier_v1. The
function classifi es every new session based on either the username or application name and
assigns the session requests and queries to a specifi c Workload group. If sessions do not
c50.indd 1136c50.indd 1136 7/31/2012 10:22:31 AM7/31/2012 10:22:31 AM
http://www.it-ebooks.info