Databases Reference
In-Depth Information
Well, prior to SQL Server 2008, you could not have done much, except for just setting
server-level parameters, such as query governor, that can control and prevent long-running
queries for a whole SQL Server instance. But what if you want to set resource restrictions on
particular queries coming from a particular application or from a particular user?
Since SQL Server 2008, we have had Resource Governor to address this situation. Resource
Governor is also present in SQL Server 2012, which you can use to manage the CPU and
memory resources on your server, based on different types of requests. These "different
types of requests" can be classified based on the source of the request, the login accounts /
request user, and role of the request user.
The functionality of Resource Governor can be divided into the following three components:
F
Classiication
F
Workload group
F
Resource pool
Here is a basic functional/architectural diagram of Resource Governor:
Session Request
Internal Workload Group
Internal Resource Pool
Default Workload Group
Default Resource Pool
Classification
(User Defined
Function)
User Workload Group-1
User Resource Pool-1
User Workload Group-2
User Resource Pool-2
The classification component defines a user-defined scalar function that is to be registered
with Resource Governor as a classifier function. Every time a request is made, the classifier
function gets executed. It identifies source requests and routes those requests to a particular
workload group, as per the rules defined in classifier function.
A workload group defines the workload by grouping multiple source requests into a single
logical unit to which the resource rules are to be applied for execution, as specified by a
particular resource pool. SQL Server creates two default workload groups, named
internal
and
default
. A workload group is mapped to a resource pool in order to route incoming query
requests to its mapped resource pool.
Search WWH ::
Custom Search