Resource Governor — Segregate Monster Queries

Overview

Honestly, resource governor is one of my favorite features in SQL Server. This feature has been introduced from SQL 2008 version and enhanced in SQL 2012. Right now, it is quite stable and practical. However, the bitter reality is that not many DBAs embrace this amazing feature. As a consultant, I had many chances to talk with DBAs in quite a few corporations. It seems to me they are either used to consider performance issues should be taken into account in design phrase(developers ‘accountability) instead of administration phrase or some cases they just heard about it but no idea how to implement it. Recalling the recent tragedy in one of my clients, I want to promote this feature once again here.

This technical article is organized to introduce resource governor feature via answering the questions of what, why, and how.

What is Resource Governor

In short, resource governor is the feature designed to allocate some certain resources mainly referring to CPU and memory (notice so far this feature does not support to limit the I/O resources) to some specific requests so as to prevent the “noisy neighbor” problem or prioritize some critical requests.

Figure 1 is the architecture of resource governor. As it illustrates, if enabling the resource governor (it is disabled by default), any coming request is classified by classification function (UDF: User Defined Function) into a specific group, in which a certain resources cap are defined. With that hard cap, the requests in that group at least or at most can only consume that limited resources.

Figure 1: Resource Governor Architecture

Why We Need Resource Governor

There are a couple of situations we need Resource Governor. First of all, we need this feature to prevent noisy neighbors. As it is often to see the servers are increasingly powerful— 8 cores or 16 cores even 32 cores. If there is only one application plugged in this server, it is kind of resource-wasting. To avoid this waste, usually what we do is to make the power database server shared by a couple of applications in terms of a few databases in one physical box either in one instance or multiple instances. Sharing is a good consideration however how we can prevent them interfere each other. In performance tuning world, we have a golden rule—20/80 principle, which means 20% poor queries grab 80% resources. Even some worse cases are that the poor query totally makes the server sink and grabs all resources so as to all other applications freeze end users as victims. Because of this, we need an efficient approach to segregate the monster queries or we call this as a “noisy neighbor” issue. Besides, applications have different priorities. Some are critical ones, who are sensitive to waiting time. On the other hand, the other might not. For example, backup process is a typical time waiting insensitive one in most cases, which means it doesn’t matter how fast it finishes in 20 minutes or in 50 minutes. On the contrary, some SLAs (Service Level Agreement) guarantee that the critical applications should not be negatively influenced by the backend backup process. With those being said, you can see resource governor is a fantastic feature somehow. Before SQL 2008, we have no way to work this out.

How to Use Resource Governor

This section shows an example how to use the resource governor. In this example, supposing we have two users, they are “VIP” and “backupAccount”. VIP user can use resources as much as he needs. However, for the user “backupAccount”, we only allocate 30% CPU usage for him.

Step 1: Create two logins “VIP” and “backupAccount”

CREATE LOGIN [VIP] WITH PASSWORD=N’abcd’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOCREATE LOGIN [backupAccount] WITH PASSWORD=N’abcd’, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Step 2: Create a resource pool with the limitation of 30% CPU consumption at most

CREATE RESOURCE POOL pMAX_CPU_PERCENT_30WITH (MAX_CPU_PERCENT = 30);GO

Step 3: Create a work group based the pool “pMAX_CPU_PERCENT_30”

CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_30USING pMAX_CPU_PERCENT_30;GO

Step 4: Create a classification function to route the user “backupAccount” into a resource-restricted work group. For other users, by default, if there is no designated work group assigned, they go into the “default” work group, which has been created without resource limitation use by system.

CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysnameWITH SCHEMABINDINGAS

BEGIN

DECLARE @workload_group_name AS sysname

IF (SUSER_NAME() = ‘backupAccount’)

SET @workload_group_name = ‘gMAX_CPU_PERCENT_30’

RETURN @workload_group_name

END;

GO

Step 5: enable the resource governor

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);GOALTER RESOURCE GOVERNOR RECONFIGURE;

GO

Step 6: run the same query in 2 sessions simultaneously with different logins to test their performance

SET NOCOUNT ONDECLARE @i INTDECLARE @s VARCHAR(100)

SET @i = 100000000

WHILE @i > 0

BEGIN

SELECT @s = @@version;

SET @i = @i – 1;

END

Performance results shown as table 1

Login Duration
VIP 4m8s
backupAccount 5m23s

(Table 1: Performance Result)

This testing result demonstrates the resource governor takes effect on the resource allocation limitation to the login “backupAccount” so as that this user perform worse than the other in running the same work load.

Step 7: (Optional) use windows “perfmon” utility to monitor the resource pool’s performance

Setting: SQL Server: Resource Pool StatsàCPU usage%–>your resource pool

(Figure 2: Setup the monitoring of resource pool)

(Figure 3: Resource Pool monitoring)

Summary

As observed and analyzed above, resource governor today as a feature plays an important role in SQL server features family. It is a breakthrough for SQL server to help all DBAs’ work defensive enough and be out of monster query nightmare.

Advertisements

Published by

Derek Dai

focusing DB, Big Data and BI tech.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s