Source: Configuring Resource Governor for SQL Server 2008 for load Balancing
Transferred from: http://www.ithov.com/server/93267.shtml
1. Why the introduction of "Resource Governor"?
Prior to SQL Server 2008, the database engine tried to provide balanced performance for all concurrent users, which gave us little control over the priority assigned to the user or workload and compounded the increase in concurrent logons. Resource Governor helps prevent performance issues and identifies applications that are in use. Resource governor controls the CPU and memory allocated to the session and further sets the maximum and minimum CPU percentages and memory that the application can use, as well as the maximum allowable degree of parallelism. Although resource Governor currently has some shortcomings, it is sufficient to handle many common resource problems.
2. Create a "resource pool"
The configuration of resource Governor starts with the creation of a resource pool. Using the Create RESOURCE Pool command in SQL Server 2008 creates a resource pool, which is used to allocate sessions and set the maximum and minimum percentage of system resources that it occupies.
For example, use the following command to create 3 resource pools based on CPU percent throttling:
CREATE RESOURCE Pool Lowpriorityappspool with (max_cpu_percent = 20); CREATE RESOURCE Pool Mediumpriorityappspool with (max_cpu_percent = 60); CREATE RESOURCE Pool Highpriorityappspool with (max_cpu_percent = 100);
The percentage of CPU used in these commands is not a hard limit. For example, if there is only one low-priority application running on the operating system, it will be able to use the entire CPU. Resource regulation based on CPU percentage will only work if parallel requirements overlap.
In addition, in resource Governor, you can establish up to 2O resource pools, 18 of which can be configured. In the other two resource pools, one is assigned to an internal process, such as a cleanup class operation, and the other is the default pool that is used for sessions that are not assigned to other pools. Therefore, you should not create too many resource pools too early, and you should carefully consider which pools are necessary.
3. Create a workload group
After grouping existing resources, we need to create a workload group to implement groups of applications that are assigned to those resource pools. Create a workload group using the Create WORKLOAD Group command in SQL Server 2008.
Here are a few examples of the Create WORKLOAD Group command application:
CREATE WORKLOAD GROUP unidentnifiedapplications USING lowpriorityappspool; CREATE WORKLOAD GROUP wellbehavedaccessapplications USING mediumpriorityappspool; CREATE WORKLOAD GROUP poorlybehavedaccessapplications USING lowpriorityappspool; CREATE WORKLOAD GROUP wellbehavedexcelapplications USING mediumpriorityappspool; CREATE WORKLOAD GROUP criticalcorporateapplications USING highpriorityappspool;
In the example above we created 5 workload groups, which were allocated to 3 resource pools. Access and Excel applications are assigned to different resource pools so that we can make changes to resources that are assigned to another resource pool in the future, based on one resource pool. Allocate fewer resources for poorly performing access applications and other applications that are not recognized. We assign the highest resource allocation priority to the identified application processes that are critical to the job.
4. View the Resource Governor configuration
After you create a workload group and assign it to a resource pool, they do not start working immediately. To determine what configuration you need to make for resource governor to work properly, you can view the current resource Governor configuration, which is displayed in a new dynamic management view.
(1). View Resource Governor configuration information
Execute command:
SELECT * from Sys.dm_resource_governor_configuration;
When the command finishes, the configuration information for the resource Governor is output. It is important to note that this output information is displayed in the Results tab of SQL Server Management Studio (SSMS). The leftmost column displays a value of 1 that is the line number of the output.
This output information tells us two information:
The first, Is_reconfiguration_pending flag indicates that the command needs to be executed before your changes take effect: ALTER RESOURCE GOVERNOR RECONFIGURE. After you run this command, the value of is_reconfiguration_pending becomes 0.
Second, the value of classifier_function_id is 0, which means that no classifier function is assigned. The classifier function is used to determine which workload group A session should be placed in.
(2). View resource pool configuration information
Execute the following command to view the resource pool configuration:
SELECT * from Sys.dm_resource_governor_resource_pools;
When the command finishes executing, you will see information about the resource pool. It is important to note that only the internal resource pool and the default resource pool are displayed in this output message until the reconfiguration command is executed.
You can use the following command to view the workload pool configuration:
SELECT * from Sys.dm_resource_governor_workload_groups;
5. Application Implementation Classification
The final step in configuring resource Governor is to determine which session you want to control. This can be achieved by using the classifier function, where the classifier assigns the session to a specific workload group.
(1). Apply App_name () function classification
The classifier function must return a value of type sysname, which is currently a nickname for nvarchar (128). For this article, we depend on the application name function (App_name ()) to determine which workload group the session should fall into, which returns the value specified in the application's connection string. I particularly like to choose this option because it supports classifying running applications, and there are several other options to choose from when classifying sessions.
The application name is not typically specified in the connection string when the application is built, but it is important to add the application name to the connection string or specify the value in the application configuration file. This method is also useful outside of resource governor because the application name appears in the trace record created by SQL Server Profiler and can be used to filter events in the trace record. Accessing the application name through a connection string also helps to track access to SQL Server when isolating application-related issues. However, some applications that are not well designed have hard-coded connection strings that are not appropriate for this approach. Hard-coded connection strings are a practice that should be avoided.
In use, the author also finds that classifying applications by name is a good starting point for identifying unknown applications that connect to the server. In this case, the advantage of the resource Governor is that the unknown application will be placed in a low-priority resource pool, and then we can quickly identify the application in use. This feature can help you determine who is using your system.
(2). Other classification functions and properties
In addition to using the App_name () function for session categorization, there are many other functions that can be implemented, such as HOST_NAME (), Suser_name (), SUSER_SNAME (), Is_srvrolemember (), and Is_member ( )。
If you decide to use HOST_NAME () or app_name () as the classifier function, be aware that the user may change these functions. But in practice, I found app_name () very useful.
In addition, some properties can be used when deciding how to categorize. The L0ginproperty () function now contains two properties (DefaultDatabase and Defauitlanguage) that can be used in the classification function. and the L0ginproperty () function provides access to the network protocol used by the connection, and also supports access authentication schemes, local IP addresses, TCP ports, and client IP addresses. The Original_db_name () function is also useful because it returns the database name provided by the session on the first connection, rather than the default database. These properties support classification based on any such value. For example, you can assign a connection to a workload group based on the database that the user typically uses.
(3). Necessary Tests before classification
It is important to test the classifier function before it is used, or the system may not respond. For example, you can test the app_name () classifier function by executing the following command in SSMS:
SELECT app_name ();
Because the classifier function does not recognize SSMS, it executes the command:
SELECT dbo. Userclassifier ();
If you enable it directly without modifying the classifier function, SSMs runs in the low-priority application resource pool. You can also use the classifier function to find the workload group name in a database table instead of hard-coding all the application names into the function. Typically, when you access a database table to find a workload group, performance is not affected too much because the database tables are not large, can be cached quickly, and can be used to make classification decisions only after the connection is established.
You can now implement the classifier function with the following command:
ALTER RESOURCE GOVERNOR with (classifier_function = dbo. Userciassifier);
the resource governor must then be reconfigured for the pending configuration to take effect. This can be done using the following command:
ALTER RESOURCE GOVERNOR RECONFIGURE;
at this point, the resource Governor configuration is complete. Then you need to make sure that the resource Governor works as expected. If the classifier function is not well tested, the system may not respond to new logon operations, and you may need to use a dedicated administrator connection (DAC) to gain access to SQL Server. If you have not yet configured the DAC, you can restart the server in single-user mode. When running in single-user mode, the connection is not constrained by the resource Governor, so you can disable resource governor until the problem is fixed.
(4). Considerations for changing the classifier function
If you need to change the classifier function, you must first disable resource governor because it cannot be changed while resource governor is running. You can disable the current classifier function by running the following command:
ALTER RESOURCE GOVERNOR with (classifier_function = NULL);
Note that the resource governor change should be performed by the most experienced DBA (database administrator) in your organization to avoid problems caused by misconfigured resource governor.
6. Other technologies for monitoring and configuring resources
In addition to resource Governor, we can monitor the CPU resources allocated by SQL Server through the system monitoring (Perfmon exe) tool. When you add a counter that you want to monitor, the SQL server:resource Pool Stats object displays an instance of each resource pool that you have configured.
Some similar instance-related selections have been added to the SQL Server:workload Group stats counter, and the associated values are also available through the query Sys.dm_os_performance_counters view.
For more careful monitoring, additional events have been added to SQL Server, including CPU Threshold exceeded, preconnectstarting, and preconnect:completed events. If you want to use. NET code to programmatically control resource governor, you can use the Resourcegovernor class that has been added to SQL Server Management Objects (SMO).
Typically, we are concerned about the maximum CPU percentage when configuring a resource pool, because the minimum percentage must be configured with great care. If you configure the minimum value too high, it is easy to over-limit the resource utilization of the system, especially the minimum percentage of memory should not be excessive.
Summary:
It should be stated that we are not able to use resource governor to reduce the priority of a query that has just been found to severely affect system performance because the workload group and resource pool are already allocated when the session is connected. Therefore, it is necessary to do a co-ordination before configuring the resource governor. While resource governor is not perfect, it is a powerful tool for DBAs to load balance resources.
Configuring resource Governor for SQL Server 2008 for load Balancing
