Skip to content

Latest commit

 

History

History
80 lines (56 loc) · 3.48 KB

change-resource-pool-settings.md

File metadata and controls

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

Change resource pool settings

[!INCLUDE SQL Server SQL MI]

You can change resource pool settings by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

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

Changing resource pool settings requires the CONTROL SERVER permission.

Change resource pool settings using SQL Server Management Studio

To change resource pool settings 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 Pools.
  2. Use the context menu for the resource pool to be modified, and select Properties.
  3. In the Resource Governor Properties page, select the row for the resource pool in the Resource pools grid.
  4. Select the cells in the row to be changed, and enter new values.
  5. To save the changes, select OK

Change resource pool settings using Transact-SQL

To change resource pool settings using [!INCLUDEtsql]:

  1. Execute the ALTER RESOURCE POOL or ALTER EXTERNAL RESOURCE POOL statement specifying the values to be changed.
  2. Execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement for the changes to take effect.

Example

The following example changes the max CPU percentage setting for the resource pool named poolAdhoc and makes the new configuration effective.

ALTER RESOURCE POOL poolAdhoc WITH (MAX_CPU_PERCENT = 25);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Related content