Data Warehouse scalability of SQL Server 2008 new features (ii)

3.10 Resource Monitor

The new Resource Monitor in SQL Server 2008 allows you to control the number of CPUs and memory resources assigned to different parts of your relational database workload. It can be used to prevent runaway queries (which prevent resources from being allocated to other workloads) and to reserve resources for important portions of your workload. The SQL Server 2005 resource policy treats all workloads equally, and shares resources on demand (for example, CPU bandwidth, memory). This can sometimes cause resource allocations to be skewed, resulting in uneven performance or unexpected speed reductions.

The primary objectives of the resource Monitor are as follows:

A. Monitoring: makes it possible to monitor resource consumption (workload groupings) for each group of requests.

B. Predictability: Enables the ability to predict the execution of workloads in environments where resource competition exists. This is done by showing the resource boundaries between the set workloads (controlled by the resource pool). The use of resource boundaries can also prevent or reduce the likelihood of a query getting out of control. The monitoring capabilities provided by the Resource Monitor make it easier to detect runaway queries.

C. Priority: Enables you to set workload priorities.

To understand the resource monitor, there are three new concepts that are important: workload grouping, resource pooling, classification (and classifier user-defined functions).

Group: A workload group, or group, is a user-specified classification of requests that is similar to the classification rules applied to each request. The value of the group exists in the resource consumption aggregation monitoring and a unified policy for all requests within the group. The group defines the policies that are used for its members.

Pool: A resource pool, or pool, that shows a portion of the physical resources of the server. Depending on its settings, the pool can have a fixed size (each of the maximum and minimum resource usage settings are equal) or share a portion between multiple pools (its minimum is less than the maximum setting available to it). In this case, sharing simply means that the resource is provided to the pool that first requested the resource. Under the default configuration, all resources are shared, so the backward-compatible SQL Server 2005 policy is maintained.

Classification: Classification is a set of user-written rules that allow the resource monitor to categorize requests into previously described groups. It is performed through a gradient Transact-SQL user-defined function (UDF), which is designed to act as a "classifier UDF" for the Resource Monitor.

These concepts are described in the following picture.

Figure 5: Resource Monitor Examples: Requests, classifications, groups, and pools

The resource monitor can be used without any application changes.