Administrating the Core Database Engine - Microsoft SQL Server 2008 R2 Administration - page 65

CREATERESOURCEPOOL priority_Team_SM_queries
WITH (MIN_CPU_PERCENT = 30,
MAX_CPU_PERCENT = 70,
MIN_MEMORY_PERCENT = 30,
MAX_MEMORY_PERCENT = 70)
GO

2. Create the next resource pool that will be reserved for ad hoc queries to reserve

maximum CPU and memory of these pools, at 25 percent, at the time of multiple
query connections and high contention on the SQL Server instance.
CREATE RESOURCE POOL ad_hoc_queries
WITH ( MIN_CPU_PERCENT = 5,
MAX_CPU_PERCENT = 25,
MIN_MEMORY_PERCENT = 5,
MAX_MEMORY_PERCENT = 25)
GO

3. The resource pool values can be modified at any time using the ALTER RESOURCE
POOL statement. Now it will be ideal to check if the settings are applied for these
resource pools by querying the following system catalog:

SELECT pool_id,name,min_cpu_percent,max_cpu_percent,
min_memory_percent,max_memory_percent
FROMsys.resource_governor_resource_pools

The results should be as follows:

As you can see from the results, the SQL Server internal resource pools and the user-defined
resource pools govern the system resources. By using SSMS, we can view the created resource
pools navigate to Management node | Resource Governor and choose Resource Pools.