SQL Server Mentalist

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

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 :

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 |