Resource Pools

               SQL Server Resource Governor introduced resource pools as a fundamental implementation of resource isolation within SQL Server instance. It represents the physical resource of the server. SQL Server Resource Pools are controlled with T-SQL and other management interfaces.

                   As a default when SQL Server is installed there are two resource pools named as Internal and Default. Internal resource pool basically represents the resources that are consumed by SQL Server itself for its running.(Resources that are reserved for SQL Server system tools). Default pool predefined user pool which contains default group. The workload runs by default on this resource pool. Therefore this Default resource pool is user configurable, but it can’t be Dropped or Create. Internal Resource pool is cant be changed.(can’t altered)

Create resource pools 

• CREATE RESOURCE POOL pool_name
  GO
  ALTER RESOURCE GOVERNOR RECONFIGURE
  GO        

Alter resource pools

• ALTER RESOURCE POOL  pool_name
  WITH (Option =value)
  GO
  ALTER RESOURCE GOVERNOR RECONFIGURE
  GO  

     

Option	Default	Internal
MIN_CPU_PERCENT	0	0
MAX_CPU_PERCENT	100	100
MIN_MEMORY_PERCENT	0	0
MAX_MEMORY_PERCENT	100	100
AFFINITY SCHEDULER	-	-
CAP_CPU_PERCENT	-	-