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

In addition to the above two types of users, there are certain users from the Senior
Management (Team SM) who require periodic information (by the way of reports) about the
sales transactional activity and stock control reports that cause intermittent performance
issues for the application. So at this juncture, your task is to keep up the performance for
the application by helping the database engine to manage its resources efficiently during
heavy usage.

Using the CREATE RESOURCE POOL command, you can create your own resource pool. The
basic syntax for this command is as follows:

CREATE RESOURCE POOL pool_name
[ WITH
( [ MIN_CPU_PERCENT = value ]
[ [ , ] MAX_CPU_PERCENT = value ]
[ [ , ] MIN_MEMORY_PERCENT = value ]
[ [ , ] MAX_MEMORY_PERCENT = value ] )]

Using the CREATE WORKLOAD GROUP statement you can create the groups; the syntax is as
follows:

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" } ]

How to do it...

In this recipe, we will use Resource Governor to create separate user-defined resource pools
on the SQL Server instances.

The first pool is classed as high-priority, which is created for the SM team to ensure that this pool
reserves a good amount of CPU and memory resources at the time of the query connection.

1. Taking into consideration the syntax mentioned previously, let us create the resource

pool and appropriate workload groups. To create the first pool for the SM team,
reserve at least 30 percent of the CPU and memory and do not exceed 70 percent.
USE master
GO