Chapter 1 Resource Governor Management (1) Use SQLServerMan

This series includes: 1. Use SQLServerManagementStudio to configure Resource Controller 2. Use T-SQL to configure Resource Controller 3. Monitor Resource Controller, we mentioned that you can improve performance by configuring multiple database servers. Such as index, statistics, hints, physical design, and server configuration. When you finish

This series includes: 1. Use SQLServer Management Studio to configure Resource Controller 2. Use T-SQL to configure Resource Controller 3. Monitor Resource Controller, we mentioned that you can improve performance by configuring multiple database servers. Such as index, statistics, hints, physical design, and server configuration. When you finish

This series includes:

1. Use SQLServer Management Studio to configure resource Governor

2. Use T-SQL to configure the Resource Controller

3. Monitor Resource Governor

Preface:

In the previous chapter, we mentioned that you can improve performance by configuring multiple database servers. Such as index, statistics, hints, physical design, and server configuration.

After you complete the above configurations, there are still a small number of stored procedures, queries are very slow, due to hardware resource restrictions, there may be no good adjustments. For example, a database server supports multiple application systems, one of which is a report system, and the report system is often very resource-consuming.

Before January 2008, it was difficult to solve this problem effectively. Starting from 2008, a Resource Controller (ResourceGovernor) was introduced to help solve such problems. Resource Controller (RG) can manage CPU and memory resources on the server. Different types of requests can be allocated to different resources.

RG functions can be divided into three components:

1. Classification)

2. Resource pool)

3. Workload group)

Basic functions/systems of RG

Classification: defines a user-defined scalar function as the RG Classification function. When a request arrives, the Classification function is executed to distinguish the request type, and put it in a specific Workload Group.

WorkloadGroup: A logical unit that contains a group of resources. The Resource Pool SQLServer creates two default workload groups, internal and default.

Resource Pool: contains the resource rule definition for the request to allocate a specific workload group. SQLServer also creates two resource pools internal and default.

Use SQLServerManagement Studio to configure resource Governor:

Before you start, create a real-world environment. Assume that the AdventureWorks database is a production database with billions of data records. This library provides multiple applications. One application is used for web programs, and the other is OLTP. Another application is the report system. When querying a report, the web program is affected. To solve this problem, you can use RG to save the CPU and memory resources of the web program. Here, the web program is retained to get at least 50% of the CPU and memory, and the report uses 25%.

This document demonstrates how to use SQLServer Management Studio (SSMS.

Preparations:

This article will create two resource pools and workload groups. Used for web and report programs. The independent user name classification function is helpful to differentiate requests. Based on the user name, the classification function sends requests to a specific workload group.

Steps:

1. Open ssms to ensure that this logon has administrator privileges. If not, you must have the alter login and control server permissions.

2. Enter in the new window. Note that the AdventureWorks2012 database is used in this article:

USE masterGO CREATE LOGIN [AW_WebAppUser] WITH PASSWORD=N'AW_WebAppUser123' ,DEFAULT_DATABASE=AdventureWorks2012GO USE AdventureWorks2012GO CREATE USER [AW_WebAppUser] FOR LOGIN [AW_WebAppUser]GOALTER ROLE [db_owner] ADD MEMBER [AW_WebAppUser]GO CREATE LOGIN [AW_ReportAppUser] WITH PASSWORD=N'AW_ReportAppUser123',DEFAULT_DATABASE=AdventureWorks2012GO USE AdventureWorks2012GOCREATE USER [AW_ReportAppUser] FOR LOGIN  [AW_ReportAppUser]GOALTER ROLE [db_owner] ADD MEMBER  [AW_ReportAppUser]GO

3. Create a category function:

USE MASTERGO CREATE FUNCTION dbo.RGClassifier( )RETURNS SYSNAME    WITH SCHEMABINDINGAS    BEGIN        DECLARE @Workload_GroupName SYSNAME           IF SUSER_NAME() = 'AW_WebAppUser'            SET @Workload_GroupName = 'rg_WebApp'           ELSE            IF SUSER_NAME() = 'AW_ReportAppUser'                SET @Workload_GroupName = 'rg_ReportApp'               ELSE                SET @Workload_GroupName = 'default'        RETURN @Workload_GroupName    END

4. Open ssms, right-click the resource controller node, and select properties. The following figure is displayed:

5. Click Enable [enable resource controller ]:

6. In the category function name drop-down box, select dbo. RGClassifier ():

7. In the resource pool grid, you can find two default resource pools defalut and internal. Now, add a new resource pool called rp_WebApp and configure it to keep in mind that the sum cannot exceed 100:

8. In the resource pool workload group rp_WebApp, create a new workload group rg_WebApp and set the CPU time to 300:

9. In the resource pool grid, add the rp_ReportApp according to the preceding steps, set the minimum CPU and memory to 25, and set the CPU time to 300:

10. Click OK to open the resource controller node:

Analysis:

After connecting to SQLServer, run the script to create a user to identify different applications to access the database. Using the account, the classification function sends login name requests to the corresponding resource pool and workload group.

Create the Class Partition Function dbo. RGClassifier () and call this function in the graphic interface. By default, resource remote control is disabled. You need to enable it manually to make it work. In addition to the graphical interface, you can also use the T-SQL statement: alter resourcegovernor reconfigure command to start.

If the request does not belong to the two newly created resource pools, it is allocated to the default workload group and the default resource pool. The Internal workload group is used internally by SQLServer, and DAC (dedicated administrator connection) is not affected by RG classification.

Finally, check whether the creation is successful on the GUI.

Extended information:

In the real world, before implementing resource tuning, you need to make a trend analysis on resource requests of various applications to help you better allocate resources.

The MIN parameters defined in the resource pool are not shared, that is, other requests cannot occupy this part and are dedicated resources. Resource controllers can have multiple resource pools. This is why the sum of percentages in MIN cannot exceed 100.

On the other hand, the value of the MAX parameter is shared, and the actual MAX value is adjusted according to the MIN value.