Home > Query, SQL > BISQL # 105 : SQL Server 2012 : Resource Governor : Introduction, Syntax, Example, Resources and Explanation with SQL Server 2012 Enhancement

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.

MIN_CPU_PERCENT =value

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.

MAX_CPU_PERCENT =value

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.

CAP_CPU_PERCENT =value

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.

AFFINITY {SCHEDULER = AUTO | (Scheduler_range_spec) | NUMANODE = (<NUMA_node_range_spec>)}

Attach the resource pool to specific schedulers. The default value is AUTO.

MIN_MEMORY_PERCENT =value

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.

MAX_MEMORY_PERCENT =value

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

For more information on SQL server visit any of my post

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 :

Link Resource Website

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 |

Categories: Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,120 other followers

%d bloggers like this: