Edit

Share via

Create a resource pool

  • Article

Applies to: SQL Server Azure SQL Managed Instance

You can create a resource pool by using SQL Server Management Studio or Transact-SQL. To understand the concepts related to resource pools, see Resource governor resource pool.

Limitations

  • The maximum CPU percentage must be equal to or higher than the minimum CPU percentage. The maximum memory percentage must be equal to or higher than the minimum memory percentage.
  • The sums of the minimum CPU percentages and minimum memory percentages for all resource pools must not exceed 100.

Permissions

Creating a resource pool requires CONTROL SERVER permission.

Create a resource pool using SQL Server Management Studio

To create a resource pool using SQL Server Management Studio:

  1. In SQL Server Management Studio, open Object Explorer and expand the Management node down to and including Resource Governor.
  2. Open the Resource Governor context menu and select Properties.
  3. In the Resource pools grid, select the empty row. This row labeled with an asterisk (*).
  4. Select the empty cell in the Name column. Enter the resource pool name.
  5. Select any other cells in the row you want to change, and enter new values.
  6. To save the changes, select OK.

Create a resource pool using Transact-SQL

To create a resource pool using Transact-SQL:

  1. Execute the CREATE RESOURCE POOL or CREATE EXTERNAL RESOURCE POOL statement specifying the property values to be set.
  2. Execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example

The following example creates a resource pool named poolAdhoc and makes the new configuration effective.

CREATE RESOURCE POOL poolAdhoc WITH (MAX_CPU_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Resource pools can govern a variety of system resources. For more information, see CREATE RESOURCE POOL.

For more samples and a complete walkthrough, see Tutorial: Resource governor configuration examples and best practices.

Related content