CREATE WORKLOAD CLASSIFIER (Transact-SQL) (Preview)

APPLIES TO: noSQL Server (starting with 2008) noAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

Creates a Workload Management Classifier. The classifier assigns incoming requests to a workload group and assigns importance 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.

Topic link icon Transact-SQL Syntax Conventions.

Syntax

CREATE WORKLOAD CLASSIFIER classifier_name  
WITH  
    ( WORKLOAD_GROUP = 'name'  
     ,MEMBERNAME = 'security_account'
 [ [ , ] IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }])
[;]

Arguments

classifier_name
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.

WORKLOAD_GROUP = 'name' 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.

WORKLOAD_GROUP should map to an existing resource class:

Static Resource Classes Dynamic Resource Classes
staticrc10 smallrc
staticrc20 mediumrc
staticrc30 largerc
staticrc40 xlargerc
staticrc50
staticrc60
staticrc70
staticrc80

MEMBERNAME = 'security_account' 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.

IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH } Specifies the relative importance of a request. Importance is one of the following:

  • LOW
  • BELOW_NORMAL
  • NORMAL (default)
  • ABOVE_NORMAL
  • HIGH

Importance influences the order in which requests are scheduled, thus giving first access to resources and locks.

Permissions

Requires CONTROL DATABASE permission.

Examples

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.

CREATE WORKLOAD CLASSIFIER wgcELTRole
  WITH (WORKLOAD_GROUP = 'staticrc20'
       ,MEMBERNAME = 'ELTRole'
      ,IMPORTANCE = above_normal);

See Also

DROP WORKLOAD CLASSIFIER (Transact-SQL)
Catalog view sys.workload_management_workload_classifier_details Catalog view sys.workload_management_workload_classifiers SQL Data Warehouse Classification