SQL Server research uses resource governor to manage resources

Objective:

In the previous chapter, you demonstrated how to use SSMS to configure resource governor. But as a DBA, there is always a time to write a script, because it can be reused and extended. And can be quickly deployed on different servers.

The following shows how to implement:

Steps:

1. Open ssms and connect to SQL Server. Ensure that the login account has controlserver permissions.

2. Run the following script to delete the previously created resource Governor object

Use Mastergodrop WORKLOAD group Rg_webappdrop RESOURCE POOL  rp_webappdrop WORKLOAD Group Rg_reportappdrop RESOURCE PO OL  Rp_reportappgo

3. Now execute the statement to create the following object, where the classification functions refer to the previous chapter:

use mastergo CREATE RESOURCE POOL [Rp_webapp]with (min_cpu_percent=50,max_cpu _percent =100,min_memory_percent =50,max_memory_percent =100) GO CREATE WORKLOAD GROUP [Rg_webapp]with (Group_max_ Requests=0,importance=medium,request_max_cpu_time_sec=300,request_max_memory_grant_percent=25,request_memory_ grant_timeout_sec=0,max_dop=0) USING [Rp_webapp]go CREATE RESOURCE POOL [Rp_reportapp]with (Min_cpu_percent=25,max_ Cpu_percent =100,min_memory_percent =25,max_memory_percent =100) GO CREATE WORKLOAD GROUP [Rg_reportapp]with (GROUP_MAX _requests=0,importance=medium,request_max_cpu_time_sec=300,request_max_memory_grant_percent=25,request_memory_ grant_timeout_sec=0,max_dop=0) USING [Rp_reportapp]go ALTER RESOURCE Governorwith (classifier_function=[dbo].[ Rgclassifier]); GO ALTER RESOURCE GOVERNOR reconfigurego 

4, in order to check whether the creation of a successful, can be queried through the DMV, here to use the following two DMV to query: Sys.dm_resource_governor_resource_pools, Sys.dm_resource_governor_ Workload_groups

Select  pool_id,        namefrom    sys.dm_resource_governor_resource_pools Select  group_id,        name,        Pool_idfrom    sys.dm_resource_governor_workload_groups

5, from the above can be seen has been created success:

The above steps can be generated by the "Script" button in the upper part of the interface while SSMs is operating, but since the T-SQL generated by SSMS is not optimized, write it yourself if you don't understand it.

Expand your knowledge:

The following configuration options are used in this article to briefly explain the resources of the Game Programming network www.cgzhw.com, which is hereby noted:

1. Importance: Defines the importance of the workload group for request processing with a value of Low/medium/high.

2. Group_max_requests: Defines the maximum number of requests that are executed in parallel in a workload group.

3. MAX_DOP: The maximum degree of parallelism for concurrent requests in a workload group.

4. Request_max_memory_grant_percent: The maximum amount of memory that can be used for a single request in a workload group.

5. Request_max_cpu_time_sec: The maximum number of seconds a single request can use for a workload group.

6. Request_memory_grant_timeout_sec: Specifies the maximum time, in seconds, that a query waits for a memory grant (work buffer memory) to become available.