创建工作负荷组Create a Workload Group

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

您可以使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL创建工作负荷组。You can create a workload group by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

限制和局限Limitations and Restrictions

REQUEST_MAX_MEMORY_GRANT_PERCENTREQUEST_MAX_MEMORY_GRANT_PERCENT

对非对齐的分区表创建索引所占用的内存与涉及的分区数成正比。The memory consumed by index creation on a non-aligned partitioned table is proportional to the number of partitions involved. 如果所需的内存总量超过工作负荷组设置为每个查询设定的限制 (REQUEST_MAX_MEMORY_GRANT_PERCENT),则这种索引创建可能会失败。If the total required memory exceeds the per-query limit, (REQUEST_MAX_MEMORY_GRANT_PERCENT) imposed by the workload group setting, this index creation may fail. 由于默认工作负荷组允许查询超过每个查询的限制,并在开始时使用所需的最低内存以便与 SQL Server 2005 保持兼容,因此,如果默认资源池配置了足够多的内存总量以运行此类查询,则用户或许能够在默认工作负荷组中运行相同的索引创建。Because the default workload group allows a query to exceed the per-query limit with the minimum required memory to start for SQL Server 2005 compatibility, the user may be able to run the same index creation in the default workload group, if the default resource pool has enough total memory configured to run such a query.

允许索引创建操作使用比最初授予的工作区内存多的工作区内存,以便提高性能。Index creation is allowed to use more memory workspace than initially granted for performance. 这个特别处理由资源调控器支持,然而,最初授予及任何其他内存授予都受工作负荷组和资源池设置的限制。This special handling is supported by Resource Governor, however, the initial grant and any additional memory grant are limited by the workload group and resource pool settings.

权限Permissions

创建工作负荷组需要 CONTROL SERVER 权限。Creating a workload group requires CONTROL SERVER permission.

使用 SQL Server Management Studio 创建工作负荷组Create a Workload Group Using SQL Server Management Studio

使用 SQL Server Management StudioSQL Server Management StudioTo create a workload group by using SQL Server Management StudioSQL Server Management Studio

  1. 在对象资源管理器中,依次逐步展开 “管理” 节点直至其中包含要修改的工作负荷组的资源池。In Object Explorer, recursively expand the Management node down to and including the resource pool that contains the workload group to be modified.

  2. 右键单击“工作负荷组” 文件夹,然后单击“新建工作负荷组” 。Right-click the Workload Groups folder, and then click New Workload Group.

  3. “资源池” 网格中,确保突出显示要添加工作负荷组的资源池。In the Resource pools grid, ensure the resource pool where you want to add the workload group is highlighted.

  4. “资源池的工作负荷组” 网格将具有一个新行,其中包含一个空名称和其他列中的默认值。The Workload groups for resource pool grid will have a new line with a blank name and default values in the other columns.

  5. 单击 “名称” 单元,然后输入工作负荷组的名称。Click the Name cell and enter a name for the workload group.

  6. 在行中单击或双击要更改其默认设置的任何其他单元,然后输入新值。Click or double-click any other cells in the row you want to change from their default settings, and enter the new values.

  7. 若要保存更改,请单击 “确定”To save the changes, click OK

使用 Transact-SQL 创建工作负荷组Create a Workload Group Using Transact-SQL

使用 Transact-SQLTransact-SQLTo create a workload group by using Transact-SQLTransact-SQL

  1. 运行 CREATE WORKLOAD GROUP 语句,指定要设置的属性值。Run the CREATE WORKLOAD GROUP statement specifying the property values to be set.

  2. 运行 ALTER RESOURCE GOVERNOR RECONFIGURE 语句。Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

示例 (Transact-SQL)Example (Transact-SQL)

以下示例创建一个名为 groupAdhoc 的工作负荷组,该组位于名为 poolAdhoc的资源池中。The following example creates a workload group named groupAdhoc in the resource pool named poolAdhoc.

CREATE WORKLOAD GROUP groupAdhoc  
USING poolAdhoc;  
GO  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO  

另请参阅See Also

资源调控器 Resource Governor
启用资源调控器 Enable Resource Governor
创建资源池 Create a Resource Pool
更改工作负荷组设置 Change Workload Group Settings
创建和测试分类器用户定义函数 Create and Test a Classifier User-Defined Function
CREATE WORKLOAD GROUP (Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL) ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE EXTERNAL RESOURCE POOL (Transact-SQL)CREATE EXTERNAL RESOURCE POOL (Transact-SQL)