Administering SQL Server workloads with Resource Governor_Microsoft SQL Server 2008 R2 Administration Cookbook-QQ阅读女生仙侠网

上QQ阅读APP看书，第一时间看更新

Administering SQL Server workloads with Resource Governor

Controlling workloads and managing server resources using a tool is a definitive choice for every DBA. Resource Governor is a new technology introduced in SQL Server 2008 that enables the DBA to manage the SQL Server workload and resources by specifying the limits on resource consumption. To manage the multiple distinct workloads, the tool will allow DBAs to differentiate these workloads to define resource pools and allocate shared resources as they are requested. It's based on the specified limits such as minimum and maximum CPU task scheduling bandwidth and reserve memory.

Internally, SQL Server database engine manages the CPU and memory resources as pools, which are default and internal. The internal resource pool is restricted and cannot be modified, using the unrestricted resources for the exclusive usage of SQL Server processes. The default resource pool is reserved for connections to the server, which can be configured to eliminate any limitations present on the resources.

Resource Governor is an Enterprise Edition feature and creating your own resource pool is the first step in managing the server resources. Using the CREATE RESOURCE POOL command, you can create your own resource pool. By default, you can associate multiple work groups with a single resource pool, but a workload group cannot be associated with multiple resource pools.

In this recipe, we will go through the process of creating multiple resource pools to govern the SQL Server workloads, which are used by the Resource Governor Tool.

Getting ready

The Inventory application database server is used by the Manufacturing team (Team M) where the server will accept connections that must run consistently without causing any downtime to the application. The Sales application database is used by the marketing team (Team S) who run ad hoc queries and reports for their day to day work purpose.

In addition to the above two types of users, there are certain users from the Senior Management (Team SM) who require periodic information (by the way of reports) about the sales transactional activity and stock control reports that cause intermittent performance issues for the application. So at this juncture, your task is to keep up the performance for the application by helping the database engine to manage its resources efficiently during heavy usage.

Using the CREATE RESOURCE POOL command, you can create your own resource pool. The basic syntax for this command is as follows:

CREATE RESOURCE POOL pool_name [ WITH ( [ MIN_CPU_PERCENT = value ] [ [ , ] MAX_CPU_PERCENT = value ] [ [ , ] MIN_MEMORY_PERCENT = value ] [ [ , ] MAX_MEMORY_PERCENT = value ] )]

Using the CREATE WORKLOAD GROUP statement you can create the groups; the syntax is as follows:

CREATE WORKLOAD GROUP group_name [ WITH ( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ] [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ] [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ] [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ] [ [ , ] MAX_DOP = value ] [ [ , ] GROUP_MAX_REQUESTS = value ] )] [ USING { pool_name | "default" } ]

How to do it...

In this recipe, we will use Resource Governor to create separate user-defined resource pools on the SQL Server instances.

The first pool is classed as high-priority, which is created for the SM team to ensure that this pool reserves a good amount of CPU and memory resources at the time of the query connection.

Taking into consideration the syntax mentioned previously, let us create the resource pool and appropriate workload groups. To create the first pool for the SM team, reserve at least 30 percent of the CPU and memory and do not exceed 70 percent.
```
USE master
GO
CREATERESOURCEPOOL priority_Team_SM_queries
WITH (MIN_CPU_PERCENT = 30,
MAX_CPU_PERCENT = 70,
MIN_MEMORY_PERCENT = 30,
MAX_MEMORY_PERCENT = 70)
GO
```
Create the next resource pool that will be reserved for ad hoc queries to reserve maximum CPU and memory of these pools, at 25 percent, at the time of multiple query connections and high contention on the SQL Server instance.
```
CREATE RESOURCE POOL ad_hoc_queries
WITH ( MIN_CPU_PERCENT = 5,
MAX_CPU_PERCENT = 25,
MIN_MEMORY_PERCENT = 5,
MAX_MEMORY_PERCENT = 25)
GO
```
The resource pool values can be modified at any time using the ALTER RESOURCE POOL statement. Now it will be ideal to check if the settings are applied for these resource pools by querying the following system catalog:
```
SELECT pool_id,name,min_cpu_percent,max_cpu_percent,
min_memory_percent,max_memory_percent
FROMsys.resource_governor_resource_pools
```

The results should be as follows:

As you can see from the results, the SQL Server internal resource pools and the user-defined resource pools govern the system resources. By using SSMS, we can view the created resource pools navigate to Management node | Resource Governor and choose Resource Pools.

Now that we have created the resource pools, the next thing we will do is create workload groups for the highest priority application connections. These connections must have high-importance on system resource usage and lowest importance application connections with less resource consumption capabilities:

--High priority applications
CREATEWORKLOADGROUP application_Sales
WITH
(IMPORTANCE = HIGH,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 75,
REQUEST_MAX_CPU_TIME_SEC = 75,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
MAX_DOP = 8,
GROUP_MAX_REQUESTS = 8 )
USING priority_Team_SM_queries
GO

--Low importance applications
CREATEWORKLOADGROUP application_adhoc
WITH
( IMPORTANCE = LOW,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
REQUEST_MAX_CPU_TIME_SEC = 40,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 540,
MAX_DOP = 1,
GROUP_MAX_REQUESTS = 4 )
USING ad_hoc_queries
GO

By default, we can assign one resource pool to a workload, even though that individual resource pool can serve multiple workload groups. In the above TSQL statement, we set the relative 'importance' of each workload group as HIGH or LOW.

Similarly, as we checked earlier to confirm user-defined resource pools were created, let's now check if workload groups are created by querying the following system catalog:

SELECT name, Importance impt,
request_max_memory_grant_percent max_m_g,request_max_cpu_time_sec max_cpu_sec,
request_memory_grant_timeout_sec m_g_to,max_dop,group_max_requests max_req,pool_id
FROMsys.resource_governor_workload_groups

The results are as follows:

Now, the important task is to create a classifier function that will be called for each new connection. Resource Governor supports user-defined functions, whose return values are used for classifying sessions that are then routed to the appropriate workload group.
This function's logic is to return the workload group where all connection requests will be sent.
The classifier function can use several different connection-related functions for use in the logic, including the system default HOST_NAME (), APP_NAME (), SUSER_NAME (), SUSER_SNAME ().

The following code is used for this function that screens the login name and connection host name in order to see to which workload group that connection should be assigned as per the above WORKLOAD group settings:

USE master
GO
CREATE FUNCTION dbo.SalesApp_PROD_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @resource_group_name sysname
IF SUSER_SNAME()IN('Login1','Login2')
SET @resource_group_name ='application_sales'
IF SUSER_SNAME()IN('Login3','Login4')
SET @resource_group_name ='application_stock'
IF HOST_NAME()IN('SalesAppHost1','SalesAppHost2')
SET @resource_group_name ='ad_hoc_queries'
-- If the resource group is still unassigned, use default
IF @resource_group_name ISNULL
SET @resource_group_name ='default'
RETURN @resource_group_name
END
GO

Note

The value for the @resource_group_name variable is default; there is a reason for this specification. If, in case, the Classifier function yields any invalid resource group then the specified login will be set as a default group.

The next step is to activate the defined classifier function and enable the configuration.

For both of these tasks, we will use the ALTER RESOURCE GOVERNOR statement:

-- Assign the classifier function
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.SalesApp_PROD_classifier)
GO
--Enable the configuration
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

You then need to perform a final check to see whether the classifier function is enabled and working, by running the following statements:
```
SELECT*FROM sys.resource_governor_configuration
```
The result will be:
Classifier_function_id is_enabled
1211151360 1
This step confirms that all the incoming application connections will be routed to the appropriate workload groups and this will allocate relevant resources as per the user-defined resource pools.

Note

In order to collect the statistics for all the incoming application connections, resource pools, and workload groups, you can query DMVs:

sys.dm_resource_governor_resource_pools

sys.dm_resource_governor_workload_groups

How it works...

The Resource Governor (RG) works on three fundamentals:

Resource pools
Workload groups
Classification

Let's see how these RG components and their relationships work with each other as they exist in the database engine environment. The functions behind the resource governor will govern the system resource usage such as CPU and memory into separate resource pools that represent the physical resources of the server. A pool has two parts; one part does not overlap with other pools by maintaining a minimum (MIN) resource reservation. The other part is shared with other pools to support maximum (MAX) possible resource consumption.

A workload group serves as a container for session requests that are similar according to the classification criteria that are applied to each request. RG predefines two workload groups, the internal group and the default group. These workload groups can be managed by using DDL statements, such as CREATE, ALTER, and DROP statements that are transactional. However, the completion of these statements does not make the changes effective. You must execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement to apply the changes.

A workload group allows the aggregate monitoring of resource consumption and the application of a uniform policy to all the requests in the group. A group defines the policies for its members.

Finally, the classification is based on a set of user-written criteria contained in a function. The logic within this function will control and enable the Resource Governor to classify sessions into existing workload groups. The only exception for this functionality is using the Dedicated Administrative Console (DAC) that shall bypass any allocation of the resource group.

Tip

The internal workload group is populated with requests that are for internal use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group.

As per the previous recipe the function dbo.SalesApp_PROD_classifier() will apply the configuration changes, then RG classifier will use the workload groups application_Sales and application_adhoc returned by the function to send a new request to the appropriate workload group. The overall context of the classification process will begin with login authentication. This is done by associating a LOGON trigger execution and using the value returned by the function to send requests to appropriate workload groups.

The execution of the classifier function and LOGON triggers is exposed in sys.dm_exec_sessions and sys.dm_exec_requests. Refer to the recipe Implementing Tuning and Monitoring performance for more information on these DMVs.

There's more...

The new functionality within SQL Server 2008 R2 version allows the System Administrators and DBAs to obtain significant control over the SQL Server instances that will have varying workload requirements with limited system resources. The Microsoft SQL Server documentation refers to a simple flow chart described as follows:

The Session 1 of n is an incoming connection (workload), which is related to a Classification (user-defined classifier function)
Session 1 of n workload is routed to a workload group
The workload group uses the resource pool; it is associated with User-defined pool
The resource pool provides (and limits) the resources required by the Application 1, Application 2 and Application 3