Microsoft® SQL Server® 2012 Bible

1138

Part VIII: Performance Tuning and Optimization

Classifi er Function

SQL Server sessions are assigned to workload groups, which in turn belong to resource

pools. You must provide a way for SQL Server to assign sessions to the correct group, which

you do with a Classifier function. These are user-defi ned functions, which can use

properties such as IP Address, Application Name, or User Name to assign a session to a

workload group. Once a session is assigned to a workload group, the sessions (and queries)

are bound by the resource constraint rules of that workload group.

The following code is an example of a Classifi er function.

CREATE FUNCTION MyClassifer()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

--workload definition based on login names

DECLARE @WorkLoadName sysname

IF SUSER_SNAME()= 'admin'

BEGIN

SET @WorkLoadName='AdminUsers';

END

--workload definition based on application

IF APP_NAME() = 'SuperApp'

BEGIN

SET @WorkLoadName='OurApps';

END

RETURN @WorkLoadName;

END

After you create a Classifi er function, you must let the Resource Governor know that you

want to use it. Following is how you assign a Classifi er function.

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = dbo.MyClassifer)

You can extend the classifi er function, for example, to consult a lookup table. However, the

classifi er function executes with each login, and you must ensure that it performs opti-

mally. Whenever there is a request for a connection, you can classify the request through

the classifi er function to identify a workload group that this session request should handle.

Now you can assign a workload group to only a single resource pool. You can use this

resource pool to assign and limit the resources required by the session.

Performance Monitoring of Resource Governor

You can check the performance through the Reliability and Performance Monitor. The

two SQL Server performance monitors available are Resource Pool Stats (as shown in

Figure 50-2).

c50.indd 1138c50.indd 1138 7/31/2012 10:22:31 AM7/31/2012 10:22:31 AM

http://www.it-ebooks.info