There are two resource pools (internal and default) are created when SQL Server 2012 is installed however more resource pools can be created by the user.
What are resource pools?
Well they represent a subset of the physical resources available to a Database Engine instance of SQL Server 2012 which intern can host one or more workload groups.
The Resource Governor Classifier identifies when a session starts and assigns the session to a workload group which are run through the resources pools.
The above diagram comes from the MSDN website which I would recommend you check out.
The function of the internal and default resource pools have the following functions:
- The internal pool represents the resources the SQL Server instance uses, which cannot be altered
- Resource consumption of the internal pool is not restricted
- Workloads in the internal pool are critical to server function
- Resource Governor enables the internal pool to pressure other pools even if it means violating the limits set for those pools
- The default pool is the first predefined user pool which can modify the default group to add more user-defined groups
- Note: you can’t remove the default group
A resource pool has two components.
The first component is exclusive and does not overlap with other pools. Allowing for minimum resource reservation for each resource pool.
The second component is shared with other pools and is used to define maximum resource consumption.
- Processor and memory resources can be assigned by specifying either a minimum (MIN) or a maximum (MAX)
- Sum of MIN values across all resource pools cannot exceed 100% of server resources.
- MAX value must be more than the MIN value, up to a value of 100%.
- If any resource pool is allocated a MIN value, the MAX value for the other pools cannot exceed the sum of the MIN values across all other pools subtracted from 100%.
if we set the MIN value for the default pool and for the internal pool to 10%, the MAX value of a third pool would automatically be adjusted to 80% of server resources
Creating a Resource Pool using the wizard
- Use Object Explorer in SQL Server Management Studio
- Right-click the Management \Resource Governor node
- Choose Properties
- In the Resource Pools grid, click the column labeled with an asterisk (*).
- In the Name column, enter the resource pool name.
- Choose a minimum or maximum CPU value and a minimum or maximum memory value.
- Click OK to save changes.
Creating a Resource Pool using Transact-SQL
CREATE RESOURCE POOL <new resource pool name> WITH (MIN_CPU_PERCENT = 20); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO