SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of.Updated 2016-11-03 Updated with TF 10210. We have written a lot about Microsoft SQL Server Trace Flags in our blog, so I thought it was about time to start making a. Brent Ozar Unlimited - SQL Server Consulting and Training. Come on down and join Brent in person, plus get access to a recorded version of the class that you can revisit later to reinforce what you learned.
The SQL Server 2. Resource Governor - SQL Shack. SQL Server Resource Governor was introduced in SQL Server 2. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption. The Resource Governor simply differentiates the incoming workload and allocates the needed CPU, Memory and IOPS resources based on the predefined limits for each workload. In this way, the SQL Server resources will be divided among the current workloads reducing the possibility of consuming all resources by single workload type, while competing for the available resources. A minimum resources limit can be also specified in the Resource Governor, which allows you to set the proper resource level for each workload type. The Resource Governor feature is very useful when you have many databases hosted in your SQL Server and many applications connecting to these databases. These connections are competing for the available SQL Server resources, affecting each other’s performance. Using the Resource Governor feature will overcome this kind of performance issue. There are three main components that form the Resource Governor; Resource Pools, Workload Groups and the Classifier. Small chunks of the CPU, Memory and IOPS resources are collectively called the Resource Pool. A set of defined connections is known as Workload Group. The component that is responsible for classifying incoming connections to workload groups, depending predefined criteria, is called the Classifier. We will review each these components, in detail, in this article. Resource Pools represent virtual instances of all of the available SQL Server CPU, Memory and IOPS resources. There are two built- in resource pools created when you install the SQL Server; the Internal Pool, which is used for the SQL Server background tasks, and the Default Pool, which is used to serve all user connections that are not directed to any user- defined resource pool. The internal pool can’t be modified, as it is used for serving SQL Server background processes. If the internal pool needs all the SQL Server available resources for a specific internal task, it will be given priority over all other user- defined pools. SQL Server supports up to 6. While creating resource pools, you need to specify the CPU, Memory and IOPS minimum and maximum limitations for each pool. The MIN_CPU_PERCENT parameter specifies the minimum CPU used by all requests in the created pool, which takes effect when CPU contentions occur, as the value will be available for other pools if there is no activity in the pool. This value can be between 0 and 1. MIN_CPU_PERCENT value for all pools not more than 1. The MAX_CPU_PERCENT parameter specifies the maximum CPU used by all requests in the created pool. Same as the MIN_CPU_PERCENT parameter, the MAX_CPU_PERCENT takes effect when CPU contentions occurred, as the value will be available for other pools if there is no activity in the pool. The MAX_CPU_PERCENT value can’t be less than the MIN_CPU_PERCENT value. For the MIN_MEMORY_PERCENT setting, it will be reserved for the pool whether it is used or not, and will not be released for the other pools if there is no activity on that pool, which will affect the overall performance if you set a high value for less frequently used pool. Again the MAX_MEMORY_PERCENT parameter should be more than the MIN_MEMORY_PERCENT value. Workload Groups are containers for user and system sessions with a common classification type that will be mapped to one resource pool. There are two built- in workload groups created when the SQL Server is installed; internal SQL Server activities are grouped into the Internal Workload Group, which is mapped to the Internal Pool, and user activities that are not directed to any user- defined workload group, which will be grouped into the Default Workload Group that is mapped to the Default Pool. There are many parameters that you can tune during the workload group creation process, such as specifying the session’s importance within the group, otherwise referred to as IMPORTANCE. Importance is compared and affected to the same resource pool. Importance can be LOW, MEDIUM and HIGH, where MEDIUM is the default value. The REQUEST_MAX_CPU_TIME_SEC parameter specifies the maximum CPU time that can be used by the session. An alert will be raised if the session exceeds that value without interrupting the session. The value 0 indicates no limit for session’s CPU time. The MAX_DOP value specifies that maximum degree of parallelism used by the parallel sessions. This value will override the default server MAXDOP value for the parallel sessions. The MAX_DOP value can be between 0 and 6. The REQUEST_MEMORY_GRANT_TIMEOUT_SEC parameter specifies the maximum time that the query will wait to be granted memory once it is available. The REQUEST_MAX_MEMORY_GRANT_PERCENT specifies the maximum memory that the session can use from the resource pool. The default value for the REQUEST_MAX_MEMORY_GRANT_PERCENT is 2. The GROUP_MAX_REQUEST specifies the maximum number of concurrent sessions that can be executed in the workload group. The Classifier is a function that is used to categorize incoming sessions into the appropriate workload group. Many system functions can be used to classify the incoming sessions such as: HOST_NAME (), APP_NAME (), SUSER_NAME (), SUSER_SNAME (), IS_SRVROLEMEMBER (), and IS_MEMBER (). Only one classifier function can be used to direct the sessions to the related workload group. It is better to keep the classifier function as simple as possible, as it will be used to evaluate each incoming session, and with complex function it will slow down the incoming queries and affect overall performance. We described each component individually, now we need to combine it all together to know how the Resource Governor works. Simply, once the session connected to the SQL Server, it will be classified using the classifier function. The session will be routed to the appropriate workload group. This workload group will use the resources available in the associated resource pool. The resource pool will provide the connected session with limited resources. The process of configuring the SQL Server Resource Governor is simple; first you need to create the resource pools, then create a workload group and map it to a resource pool, after that the classification function should be created to classify the incoming requests and finally the resource governor will be enabled with the created classification function. Let’s start with creating two resource pools, the Service. Pool that will be used for the service accounts and the User. Pool that will be used for the user accounts as below. CREATE RESOURCE POOL [Service. Pool] WITH(. min_cpu_percent=5. AFFINITY SCHEDULER = AUTO. CREATE RESOURCE POOL [User. Pool] WITH(. min_cpu_percent=0. AFFINITY SCHEDULER = AUTO. GO1. 23. 45. 67. 89. CREATE RESOURCE POOL[Service. Pool]WITH(min_cpu_percent=5. AFFINITY SCHEDULER=AUTO)GOCREATE RESOURCE POOL[User. Pool]WITH(min_cpu_percent=0,max_cpu_percent=3. AFFINITY SCHEDULER=AUTO)GO. As you can see, the Service. Pool assigned resources more than the User. Pool, in order to give priority for the requests coming from the application side over the users’ ad- hoc queries. Now the two resource pools are ready, we will start creating two workload groups; Service. Group that will be mapped to the Service. Pool resource pool and the User. Group that will be mapped to User. Pool resource pool, keeping all other parameters with its default values as follows. CREATE WORKLOAD GROUP [Service. Group]. USING [Service. Pool]. CREATE WORKLOAD GROUP [User. Group]. USING [User. Pool]. GOCREATE WORKLOAD GROUP[Service. Group]USING[Service. Pool]GOCREATE WORKLOAD GROUP[User. Group]USING[User. Pool]GO. To make sure that the changes will take effect, the RECONFIGURE statement should be run as below. ALTER RESOURCE GOVERNOR RECONFIGURE. GOALTER RESOURCE GOVERNOR RECONFIGURE; GO. Now that the resource pools and workload groups have been created and configured successfully, ou can now create the resource pools and workload groups simply using SQL Server Management Studio. Expand the Management node from the Object Explorer, then right- click on the Resource Governor and choose New Resource Pools as follows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |