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

A resource pool contains the definition of resource rules that are to be applied to incoming
query requests being routed by a particular workload group to which a resource pool is
mapped. SQL Server creates two default resource pools, named internal and default . A
resource pool is mapped to a workload group to apply resource rules to incoming query
requests routed by its mapped workload group.
Configuring Resource Governor with SQL
Server Management Studio
Before we look at configuring Resource Governor, we will first set up a real-life scenario.
Let's suppose that AdventureWorks2012 is our production database and it has billions
of records. The database supports multiple applications. One of the applications supported
by the database is a web application, which is an OLTP database application that consumes
AdventureWorks2012 database. The normal functioning of the web application is very
important compared to any other applications consuming the database server resources.
Another application is the reporting application that is used to generate reports. To fulfil
reporting requirements, this application runs queries with heavy calculations. Because of
heavy calculations, queries take longer to execute and consume a high percentage of CPU
and memory resources, which leaves less room for other query requests, made by the web
application, to execute efficiently. Due to this, the web application faces performance issues.
To solve this problem, we will configure the Resource Governor in such a way that, even in
conditions of resource contention caused by multiple application requests, query requests
coming from the web application get a minimum of 50 percent of CPU and memory resources,
and query requests coming from the reporting application get a minimum of 25 percent of
CPU and memory resources. This way, by restricting resources for the reporting application,
the web application can have more room to execute its queries smoothly.
In this recipe, we will learn how to set up and configure Resource Governor with SQL Server
Management Studio.
Getting ready
To address the mentioned problem, we will create two pairs of a resource pool and a workload
group each; one of these pairs is associated with the web application and the other with the
reporting application.
The resource pool associated with the web application will be configured such that it has at
least 50 percent of CPU and memory resources available at the time of resource contention.