Databases Reference
In-Depth Information
Next, we executed the script that created the required resource pools and workload groups.
To create resource pools, we used the
CREATE
RESOURCE
POOL
command, and to create
workload groups we used the
CREATE
WORKLOAD
GROUP
command.
We first created the
rp_WebApp
resource pool and the
rg_WebApp
workload group using the
rp_WebApp
resource pool. This resource pool is used by the web application.
We then created the
rp_ReportApp
resource pool and the
rg_ReportApp
workload group,
using the
rp_ReportApp
resource pool. This resource pool is used by the reporting application.
The script also registered the
[dbo].[RGClassifier]
function as a classifier function
with the
ALTER
RESOURCE
GOVERNOR
syntax. At the end of script, we executed the
ALTER
RESOURCE
GOVERNOR
RECONFIGURE
command for the applied changes to take effect.
If you make any changes in the Resource Governor configuration, you
must execute the
ALTER
RESOURCE
GOVERNOR
RECONFIGURE
command for new changes to take effect.
Finally, we verified that the script created the Resource Governor objects required, by querying
dynamic management views
sys.dm_resource_governor_resource_pools
and
sys.
dm_resource_governor_workload_groups
.
There's more...
While configuring workload groups, the following parameters should be kept in mind:
F
IMPORTANCE: It specifies the relative importance of the requests in a workload
group. The value can be
LOW
,
MEDIUM
, or
HIGH
.
F
GROUP_MAX_REQUESTS: It specifies the maximum number of requests that can be
executed in parallel in a workload group.
F
MAX_DOP: It specifies the maximum degree of parallelism for parallel requests in a
workload group.
F
REQUEST_MAX_MEMORY_GRANT_PERCENT: It is the maximum memory in
percentage that a single request in a workload group can use.
F
REQUEST_MAX_CPU_TIME_SEC: It is the maximum amount of time in seconds that
a single request can use in a workload group.
F
REQUEST_MEMORY_GRANT_TIMEOUT_SEC: It is the maximum amount of time in
seconds that a query can wait for, for memory.
For more information on Resource Governor, refer to the product documentation for SQL
Server 2012 at
http://msdn.microsoft.com/en-us/library/bb933866.aspx
.
Search WWH ::
Custom Search