You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: docs/relational-databases/system-catalog-views/sys-workload-management-workload-classifier-details-transact-sql.md
@@ -31,4 +31,4 @@ Requires Control Database permission.
31
31
32
32
## Next steps
33
33
34
-
For a list of all the catalog views for SQL Data Warehouse and Parallel Data Warehouse, see [SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md). To create a workload classifier see [CREATE WORKLOAD CLASSIFIER](../../t-sql/statements/create-workload-classifier-transact-sql.md)
34
+
For a list of all the catalog views for SQL Data Warehouse and Parallel Data Warehouse, see [SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md). To create a workload classifier, see [CREATE WORKLOAD CLASSIFIER](../../t-sql/statements/create-workload-classifier-transact-sql.md)
Copy file name to clipboardExpand all lines: docs/relational-databases/system-catalog-views/sys-workload-management-workload-classifiers-transact-sql.md
|classifier_id|**int**|Unique ID of the classifier. Is not nullable||
24
-
group_name|**sysname**|Name of the workload group the classifier is assigned to. Is not nullable. <br /><br />Joinable to [sys.workload_management_workload_groups](sys-workload-management-workload-groups-transact-sql.md)||
25
+
group_name|**sysname**|Name of the workload group the classifier is assigned to. Is not nullable. </br>Joinable to [sys.workload_management_workload_groups](sys-workload-management-workload-groups-transact-sql.md)||
25
26
name|**sysname**|Name of the classifier. Must be unique to the instance. Is not nullable.||
26
27
|importance|**sysname**|Is the relative importance of a request in this workload group and across workload groups for shared resources. Importance specified in the classifier overrides the workload group importance setting. Is not nullable. |low, below_normal, normal, above_normal, high |
27
28
|create_time|**datetime**|Time the classifier was created.||
28
29
modify_time|**datetime**|Time the classifier was last modified.||
29
-
is_enabled|**bit**|Displays whether the classifier is enabled or not. Is not nullable.|0 = the classifier is not enabled <br /><br /> 1 = the classifier is enabled|
30
-
30
+
is_enabled|**bit**|Displays whether the classifier is enabled or not. Is not nullable.|0 = the classifier is not enabled </br> 1 = the classifier is enabled|
31
31
32
32
## Permissions
33
-
Requires CONTROL DATABASE permission.
34
33
34
+
Requires CONTROL DATABASE permission.
35
35
36
-
## Next steps
37
-
For a list of all the catalog views for SQL Data Warehouse and Parallel Data Warehouse, see [SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md). To create a workload classifier see [CREATE WORKLOAD CLASSIFIER](../../t-sql/statements/create-workload-classifier-transact-sql.md)
38
-
36
+
## Next steps
39
37
38
+
For a list of all the catalog views for SQL Data Warehouse and Parallel Data Warehouse, see [SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md). To create a workload classifier, see [CREATE WORKLOAD CLASSIFIER](../../t-sql/statements/create-workload-classifier-transact-sql.md)
@@ -25,17 +26,16 @@ Returns information for each workload group.
25
26
|importance|**sysname**|Is the relative importance of a request in this workload group and across workload groups for shared resources. Is not nullable.|low, below_normal, normal, above_normal, high|
26
27
|min_percentage_resource|**tinyint**|Guaranteed amount of resources for all requests in the workload group. Resources are not shared with other resource pools. Is not nullable.||
27
28
|cap_percentage_resource|**tinyint**|Hard cap on the resource percentage allocation for all requests in the workload group. Limits the maximum resources allocated to the specified level. The allowed range for value is from 1 through 100.||
28
-
|request_min_resource_grant_percent|**decimal (5,2)**|Specifies the minimum amount of resources allocated to a request. The value is a decimal with a default value of 10. The allowed range for value is from 0.08 to 100.<br <br />Depending on availability of unreserved resources, the value of cap_percentage_resource and the number of requests executing in the workload group, more resources may be allocated to the request.||
29
-
|query_execution_timeout_sec|**int**|The amount of execution time, in seconds, allowed before the query is canceled. Queries cannot be canceled once they have reached the return phase of execution. query_execution_timeout_sec does not include time spent queued.||
30
-
|query_wait_timeout_sec|**int**|The amount of time a request can queue, in seconds, before it is canceled.||
29
+
|request_min_resource_grant_percent|**decimal (5,2)**|The minimum amount of resources allocated to a request. This value is a decimal with a default value of 10. The allowed range for this value is from 0.08 to 100.</br>More resources may be allocated to the request based on availability of unreserved resources, the value of cap_percentage_resource and the number of requests executing in the workload group.||
30
+
|query_execution_timeout_sec|**int**|The amount of execution time, in seconds, allowed before the query is canceled. Queries can't be canceled once they've reached the return phase of execution. query_execution_timeout_sec doesn't include time spent queued.||
31
+
|query_wait_timeout_sec|**int**|The amount of time a request can queue, in seconds, before it's canceled.||
31
32
|create_time|**datetime**|Time the workload group was created||
32
33
|modify_time|**datetime**|Time the workload group was last modified||
33
34
34
35
## Permissions
35
-
Requires Control Database permission.
36
36
37
+
Requires Control Database permission.
37
38
38
-
## Next steps
39
-
For a list of all the catalog views for SQL Data Warehouse and Parallel Data Warehouse, see [SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md). To create a workload classifier see [CREATE WORKLOAD CLASSIFIER](../../t-sql/statements/create-workload-classifier-transact-sql.md)
40
-
39
+
## Next steps
41
40
41
+
For a list of all the catalog views for SQL Data Warehouse and Parallel Data Warehouse, see [SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md). To create a workload classifier, see [CREATE WORKLOAD CLASSIFIER](../../t-sql/statements/create-workload-classifier-transact-sql.md)
Creates a Workload Management Classifier. The classifier assigns incoming requests to a workload group based on the parameters specified in the classifier statement definition. Classifiers are evaluated with every request submitted. If a request is not matched to a classifier, it is assigned to the default workload group. The default workload group is the smallrc resource class.
@@ -31,24 +32,24 @@ Creates a Workload Management Classifier. The classifier assigns incoming reque
31
32
32
33
## Syntax
33
34
34
-
```
35
+
```t-sql
35
36
CREATE WORKLOAD CLASSIFIER classifier_name
36
37
WITH
37
38
( WORKLOAD_GROUP = ‘name’
38
-
,MEMBERNAME = ‘security_account’
39
+
,MEMBERNAME = ‘security_account’
39
40
[ [ , ] IMPORTANCE = { LOW | BELOW NORMAL | NORMAL | ABOVE NORMAL | HIGH }])
40
41
[;]
42
+
```
41
43
42
-
```
43
-
44
-
## Arguments
44
+
## Arguments
45
+
45
46
*classifier_name*
46
47
Specifies the name by which the workload classifier is identified. classifier_name is a sysname. It can be up to 128 characters long and must be unique within the instance.
47
48
48
49
WORKLOAD_GROUP = *'name'*
49
50
When the conditions are met by the classifier rules, name maps the request to a workload group. name is a sysname. It can be up to 128 characters long and must be a valid workload group name at the time of classifier creation.
50
51
51
-
For the purposes of the WLM Importance preview, WORKLOAD_GROUP should map to an existing resource class:
52
+
WORKLOAD_GROUP should map to an existing resource class:
@@ -64,40 +65,37 @@ For the purposes of the WLM Importance preview, WORKLOAD_GROUP should map to an
64
65
MEMBERNAME = *'security_account'*
65
66
This is the security account being added to the role. Security_account is a sysname, with no default. Security_account can be a database user, database role, Azure Active Directory login, or Azure Active Directory group.
66
67
67
-
IMPORTANCE = { LOW | BELOW NORMAL | NORMAL | ABOVE NORMAL | HIGH }
68
+
IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }
68
69
Specifies the relative importance of a request. Importance is one of the following:
69
70
71
+
- LOW
72
+
- BELOW_NORMAL
73
+
- NORMAL (default)
74
+
- ABOVE_NORMAL
75
+
- HIGH
70
76
77
+
Importance influences the order in which requests are scheduled, thus giving first access to resources and locks.
71
78
72
-
- LOW
73
-
- BELOW_NORMAL
74
-
- NORMAL (default)
75
-
- ABOVE_NORMAL
76
-
- HIGH
77
-
78
-
Importance influences the order which requests are scheduled, thus giving first access to resources and locks.
79
+
## Permissions
79
80
80
-
> [!NOTE]
81
-
> Internally each importance setting is stored as a number that is used for calculations.
82
-
83
-
## Permissions
84
81
Requires CONTROL DATABASE permission.
85
82
86
-
## Examples
83
+
## Examples
84
+
87
85
The following example shows how to create a workload classifier named `wgcELTRole`. It uses the staticrc20 workload group, the user `ELTRole`, and sets the importance to `above_normal`.
Specifies the name by which the workload classifier is identified. classifier_name is a sysname. It can be up to 128 characters long and must be unique within the instance.
42
+
Specifies the name by which the workload classifier is identified. classifier_name is a sysname. It can be up to 128 characters long and must be unique within the instance.
42
43
43
-
## Remarks
44
+
## Remarks
45
+
44
46
The DROP WORKLOAD CLASSIFIER statement is not allowed on the default workload classifier.
45
-
46
-
When you are executing DDL statements, we recommend that you be familiar with Resource Governor states. For more information, see [Resource Governor](../../relational-databases/resource-governor/resource-governor.md).
47
-
48
-
If a workload group contains active sessions, dropping or moving the workload group to a different resource pool will fail when the ALTER RESOURCE GOVERNOR RECONFIGURE statement is called to apply the change. To avoid this problem, you can take one of the following actions:
49
-
50
-
- Wait until all the sessions from the affected group have disconnected, and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
51
-
52
-
- Explicitly stop sessions in the affected group by using the KILL command, and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
53
-
54
-
- Restart the server. After the restart process is completed, the deleted group will not be created, and a moved group will use the new resource pool assignment.
55
-
56
-
- In a scenario in which you have issued the DROP WORKLOAD GROUP statement but decide that you do not want to explicitly stop sessions to apply the change, you can re-create the group by using the same name that it had before you issued the DROP statement, and then move the group to the original resource pool. To apply the changes, run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
57
-
58
-
## Permissions
47
+
48
+
## Permissions
49
+
59
50
Requires CONTROL DATABASE permission.
60
51
61
-
## Examples
52
+
## Examples
53
+
62
54
The following example drops the workload classifier named `wgcELTROLE`.
63
55
64
-
```
56
+
```
65
57
DROP WORKLOAD CLASSIFIER wgcELTRole;
66
58
```
67
59
68
60
> [!NOTE]
69
61
> A request submitted without a matching classifier, is classified to the default workload group. The default workload group is currently the smallrc resource class.
0 commit comments