BISQL # 105 : SQL Server 2012 : Resource Governor : Introduction, Syntax, Example, Resources and Explanation with SQL Server 2012 Enhancement
Hi Friends
In this post we are going to cover for topics “Resource Governor”
- Introduction to Resource Governor:
- How to Enable / Disable Resource Governor
- What does Resource governor Does
- Syntax for create resource pool
- Explanation for above argument
- Example with pool TaskPool
- The enhancements in SQL server 2012
- Additional Information
Introduction to Resource Governor:
Resource Governor is a feature introduced in SQL server 2008. It requires Enterprise and developer edition.
The resources allocation for users, resources can be controlled are “Memory and CPU” no IO can be controlled.
How to Enable / Disable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE GO ALTER RESOURCE GOVERNOR DISABLE; GO
What does Resource governor Does
With Resource governor we can control the resource to be allocated to the each individual/group in such a way that priority users works uninterruptable.
The enhancements to the Resource Governor enable you to more effectively govern performance in multi-tenancy environments like private cloud
Syntax for create resource pool
CREATE RESOURCE POOL pool_name [ WITH ( [ MIN_CPU_PERCENT = value ] [ [ , ] MAX_CPU_PERCENT = value ] [ [ , ] CAP_CPU_PERCENT = value ] [ [ , ] AFFINITY {SCHEDULER = AUTO | (Scheduler_range_spec) | NUMANODE = (NUMA_node_range_spec)} ] [ [ , ] MIN_MEMORY_PERCENT = value ] [ [ , ] MAX_MEMORY_PERCENT = value ]) ] [;] Scheduler_range_spec::= {SCHED_ID | SCHED_ID TO SCHED_ID}[,…n] NUMA_node_range_spec::= {NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID}[,…n]
Explanation for above argument
pool_name
Is the user-defined name for the resource pool. pool_name is alphanumeric, can be up to 128 characters, must be unique within an instance of SQL Server, and must comply with the rules for identifiers.
Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention. value is an integer with a default setting of 0. The allowed range for value is from 0 through 100.
Specifies the maximum average CPU bandwidth that all requests in resource pool will receive when there is CPU contention. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Attach the resource pool to specific schedulers. The default value is AUTO.
Specifies the minimum amount of memory reserved for this resource pool that can not be shared with other resource pools. value is an integer with a default setting of 0 The allowed range for value is from 0 to 100.
Specifies the total server memory that can be used by requests in this resource pool. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.
Example with pool TaskPool
CREATE RESOURCE POOL TaskPool; GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO CREATE RESOURCE POOL TaskPool WITH ( MIN_CPU_PERCENT = 10, MAX_CPU_PERCENT = 20, CAP_CPU_PERCENT = 30, AFFINITY SCHEDULER = AUTO, MIN_MEMORY_PERCENT = 5, MAX_MEMORY_PERCENT = 15);
The enhancements in SQL server 2012 are
- Support for 64 resource pools
- Greater CPU usage control
- Resource pool affinity for partitioning of physical resources and predictable resource allocation
Additional Information
The following topics provide additional information
- ALTER RESOURCE POOL (Transact-SQL)
- CREATE RESOURCE POOL (Transact-SQL)
- sys.dm_exec_query_resource_semaphores (Transact-SQL)
- sys.dm_resource_governor_resource_pool_affinity (Transact-SQL)
- sys.dm_resource_governor_resource_pools (Transact-SQL)
- sys.dm_exec_query_memory_grants (Transact-SQL)
- sys.resource_governor_workload_groups (Transact-SQL)
For more information on SQL server visit any of my post
- BISQL # 104 : ‘A to Z’ SQL Command # 3 : Checkpoints
- Microsoft Press : Free eBook: Introducing Windows Server 2012
- BISQL # 103 : ‘A to Z’ SQL Command # 2 : ALTER USER
- BISQL # 102 : Discontinued Database Engine Functionality in SQL Server 2012
- BISQL # 101 : ‘A to Z’ SQL Command # 1 : ALTER DATABASE
- List of Monthly post of MS BI,SQL & Link Blog – June 2012
- BISQL # 99 – ‘A to Z’ SQL Command # 1 – List of Command which are present in SQL Server 2008 R2 AND SQL Server 2012
- Starting of Brand New Series – ‘A to Z’ SQL Command Series
- List of Monthly post of MS BI,SQL & Link Blog – May 2012
- BISQL # 98 – Introduction of Private Cloud with SQL Server and Hyper-V, Resource Link
- Security Guide for SAP on SQL Server 2012 – Whitepaper Released
- BISQL #97 – Laymen to SQL Developer # 9 – Assignment #2 – Relational Model, Schema, Attribute, Database, Key & Relational Query
Hope this helps and you like this post on Resource Governor and its Introduction, Syntax, Example, Resources and Explanation with SQL Server 2012 Enhancement !!
Those who have not ye subscribe my Blog yet they can subscribe it !So that I can post you @ real time and all sort of knowledge in your mail without Zero spamming !!
Happy Learning and Sharing !!
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |
-
March 25, 2013 at 10:59 pmBI SQL # 109 : Information on SQL Server 2012 Licensing Core-based and Server/CAL Models | SQL Server Mentalist
SQL Server Mentalist 