Skip to content

Latest commit

 

History

History
87 lines (61 loc) · 4.04 KB

create-a-resource-pool.md

File metadata and controls

87 lines (61 loc) · 4.04 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Create a Resource Pool
Learn how to create a resource pool using SQL Server Management Studio or Transact-SQL.
WilliamDAssafMSFT
wiassaf
dfurman
01/02/2025
sql
performance
how-to
resource pools [SQL Server], create
Resource Governor, resource pool create
>= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current

Create a resource pool

[!INCLUDE SQL Server SQL MI]

You can create a resource pool by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. 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 [[!INCLUDEssManStudioFull]](../../ssms/download-sql-server-management-studio-ssms.md):

  1. In [!INCLUDEssManStudioFull], 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 [!INCLUDEtsql]:

  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