Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server 2012 Property Editors › Define a SQL Server 2012 Resource Pool

Define a SQL Server 2012 Resource Pool

You use resource pools in SQL Server 2012 to manage the resource policies for session requests. A workload group allows the aggregate monitoring of resource consumption and the application of a uniform policy to all the requests in the group.

Use the SQL Server Resource Pool Editor to define the physical resource parameters for a virtual resource pool within a SQL Server instance.

To define a SQL Server resource pool

1. Click Target - SQL Server, Resource Pools on the Model menu.

   The SQL Server Resource Pool Editor opens.

2. Select the resource pool in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a resource pool. Use the Enter filter text box to filter a large list of resource pools to locate the one that you want to define.

   Name

   Specifies the name of the resource pool. You can change the name of the resource pool in this field.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

   Minimum CPU Percentage

   Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention.

   Maximum CPU Percentage

   Specifies the maximum average CPU bandwidth for all requests in the resource pool when there is CPU contention.

   Hard Cap CPU Percentage

   Specifies the hard maximum average CPU bandwidth for all requests in the resource pool. The CPU capacity available above this value is not used.

   Affinity

   Specifies the schedulers to which the resource pool is attached to. You can specify a range of schedulers or NUMA nodes.

   Affinity Value

   Specifies the range of specific schedulers that you want to attach the resource pool to. For example, 0 to 35.

   Minimum Memory Percentage

   Specifies the guaranteed minimum amount of memory for all requests in the resource pool.

   Maximum Memory Percentage

   Specifies the maximum percentage of total memory that can be used by requests in the resource pool.

3. (Optional) Click the Comment tab and enter any comments that you want to associate with the resource pool.
4. (Optional) Click the Where Used tab to view where the resource pool is used in the model.
5. (Optional) Click the UDP tab to work with user-defined properties for the resource pool.
6. (Optional) Click the Notes tab to view history information and view or edit user notes.
7. Click Close.

   The resource pool is defined and the SQL Server Resource Pool Editor closes.