SQL Server research uses resource governor to manage resources

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.