Administrating the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 66

Now that we have created the resource pools, the next thing we will do is create workload
groups for the highest priority application connections. These connections must have
high-importance on system resource usage and lowest importance application connections
with less resource consumption capabilities:
--High priority applications
CREATEWORKLOADGROUP application_Sales
WITH
(IMPORTANCE = HIGH,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 75,
REQUEST_MAX_CPU_TIME_SEC = 75,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
MAX_DOP = 8,
GROUP_MAX_REQUESTS = 8 )
USING priority_Team_SM_queries
GO
--Low importance applications
CREATEWORKLOADGROUP application_adhoc
WITH
( IMPORTANCE = LOW,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
REQUEST_MAX_CPU_TIME_SEC = 40,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 540,
MAX_DOP = 1,
GROUP_MAX_REQUESTS = 4 )
USING ad_hoc_queries
GO
By default, we can assign one resource pool to a workload, even though that individual
resource pool can serve multiple workload groups. In the above TSQL statement, we set the
relative 'importance' of each workload group as HIGH or LOW .
Similarly, as we checked earlier to confirm user-defined resource pools were created, let's now
check if workload groups are created by querying the following system catalog:
SELECT name, Importance impt,
request_max_memory_grant_percent max_m_g,request_max_cpu_time_sec max_
cpu_sec,
request_memory_grant_timeout_sec m_g_to,max_dop,group_max_requests
max_req,pool_id
FROMsys.resource_governor_workload_groups