Microsoft® SQL Server® 2012 Bible

1134

Part VIII: Performance Tuning and Optimization

CREATE RESOURCE POOL CustomPool1

WITH (MAX_CPU_PERCENT = 30);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

You can set the minimum and maximum CPU values as well as the minimum and maximum

memory values with the CREATE RESOURCE POOL statement as shown here.You’ll also set a

hard cap of 40 percent and restrict this resource pool to only use NUMA node 0.

DROP RESOURCE POOL CustomPool1;

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

CREATE RESOURCE POOL CustomPool1

WITH( MIN_CPU_PERCENT = 5,

MAX_CPU_PERCENT = 30,

CAP_CPU_PERCENT = 40,

AFFINITY NUMANODE = (0),

MIN_MEMORY_PERCENT = 5,

MAX_MEMORY_PERCENT = 30);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

You can increase the maximum CPU value to 35 with the following SQL statement.

ALTER RESOURCE POOL CustomPool1

WITH( MIN_CPU_PERCENT = 5,

MAX_CPU_PERCENT = 35,

MIN_MEMORY_PERCENT = 5,

MAX_MEMORY_PERCENT = 30);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

You can also create resource pools with SQL Server Management Studio. Even before creat-

ing resource pools, you must enable the Resource Governor. The following steps enable the

Resource Governor and then add a resource pool named CustomPool2.

1. Open SQL Server Management Studio.


2. Open the instance in which you want to enable the Resource Governor.


3. Open the Management folder.


4. Right-click Resource Governor; a menu appears.


5. Click Enable.


6. Drill down on the Resource Governor, and open the Resource Pools folder.


7. Right-click the Resource Pools folder; a menu appears.

c50.indd 1134c50.indd 1134 7/31/2012 10:22:30 AM7/31/2012 10:22:30 AM

http://www.it-ebooks.info