Resource Management with Resource Governor - Microsoft SQL Server 2012 Performance Tuning - page 428

12. Expand the Resource Governor node under the Management node in Object
Explorer. After creating the resource pools and workload groups, you will see
them in Object Explorer, as shown in following screenshot:
How it works...
After connecting to the SQL server, we first executed a script, which by using the CREATE
LOGIN T-SQL command, created the two following login accounts for web application and
reporting application, respectively:
F AW_WebAppUser
F AW_ReportAppUser
The script also created corresponding users for these two login accounts in the
AdventureWorks2012 database by using the CREATE USER T-SQL command. The new
users were added to the db_owner database role by executing the ALTER ROLE [db_
owner] command.
After creating the required login accounts and users in the AdventureWorks2012 database,
we executed a script that created a user-defined scalar function— dbo.RGClassifier() . The
purpose of this function is to identify the incoming session requests, classify them on the basis
of their current user, and route them to their appropriate workload groups. Note that we used
the SUSER_SNAME() system function, which returns the user initiating the current request.
In the SUSER_SNAME() function, a condition is checked against the current user. If the user
is AW_WebAppUser (query request coming from the web application), the workload group
name rg_WebApp is assigned to the variable @Workload_GroupName . If the user is AW_
ReportAppUser (query request coming from the reporting application), the workload group
rg_ReportApp is assigned to the variable @Workload_GroupName . In all the other cases,
the default workload group name is stored in the @Workload_GroupName variable.