You can create a workload group by using SQL Server Management Studio or Transact-SQL.
The memory consumed by index creation on a non-aligned partitioned table is proportional to the number of partitions involved. 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. 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.
Creating a workload group requires CONTROL SERVER permission.
To create a workload group by using SQL Server Management Studio
In Object Explorer, recursively expand the Management node down to and including the resource pool that contains the workload group to be modified.
Right-click the Workload Groups folder, and then click New Workload Group.
In the Resource pools grid, ensure the resource pool where you want to add the workload group is highlighted.
The Workload groups for resource pool grid will have a new line with a blank name and default values in the other columns.
Click the Name cell and enter a name for the workload group.
Click or double-click any other cells in the row you want to change from their default settings, and enter the new values.
To save the changes, click OK
To create a workload group by using Transact-SQL
Run the CREATE WORKLOAD GROUP statement specifying the property values to be set.
Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
The following example creates a workload group named
groupAdhoc in the resource pool named
CREATE WORKLOAD GROUP groupAdhoc USING poolAdhoc; GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO
Enable Resource Governor
Create a Resource Pool
Change Workload Group Settings
Create and Test a Classifier User-Defined Function
CREATE WORKLOAD GROUP (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE EXTERNAL RESOURCE POOL (Transact-SQL)