Quickstart: Create a workload classifier using T-SQL

In this quickstart, you'll quickly create a workload classifier with high importance for the CEO of your organization. This workload classifier will allow CEO queries to take precedence over other queries with lower importance in the queue.

If you don't have an Azure subscription, create a trial Azure account before you begin.

This quickstart assumes you have already provisioned a dedicated SQL pool in Azure Synapse Analytics and that you have CONTROL DATABASE permissions. If you need to create one, use Create and Connect - portal to create a dedicated SQL pool called mySampleDataWarehouse.

Sign in to the Azure portal.

Create a SQL Server authentication login in the master database using CREATE LOGIN for 'TheCEO'.

IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'TheCEO')
BEGIN
CREATE LOGIN [TheCEO] WITH PASSWORD='<strongpassword>'
END
;

Create user, "TheCEO", in mySampleDataWarehouse

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'THECEO')
BEGIN
CREATE USER [TheCEO] FOR LOGIN [TheCEO]
END
;

Create a workload classifier for "TheCEO" with high importance.

DROP WORKLOAD CLASSIFIER [wgcTheCEO];
CREATE WORKLOAD CLASSIFIER [wgcTheCEO]
WITH (WORKLOAD_GROUP = 'xlargerc'
      ,MEMBERNAME = 'TheCEO'
      ,IMPORTANCE = HIGH);

SELECT * FROM sys.workload_management_workload_classifiers

DROP WORKLOAD CLASSIFIER [wgcTheCEO]
DROP USER [TheCEO]
;

You're being charged for data warehouse units and data stored in your dedicated SQL pool. These compute and storage resources are billed separately.

• If you want to keep the data in storage, you can pause compute when you aren't using the dedicated SQL pool. By pausing compute, you're only charged for data storage. When you're ready to work with the data, resume compute.
• If you want to remove future charges, you can delete the dedicated SQL pool.

Follow these steps to clean up resources.

1. Sign in to the Azure portal, select your dedicated SQL pool.

   

2. To pause compute, select the Pause button. When the dedicated SQL pool is paused, you see a Start button. To resume compute, select Start.

3. To remove the dedicated SQL pool so you're not charged for compute or storage, select Delete.

• You've now created a workload classifier. Run a few queries as TheCEO to see how they perform. See sys.dm_pdw_exec_requests to view queries and the importance assigned.
• For more information about dedicated SQL pool workload management, see Workload Importance and Workload Classification.
• See the how-to articles to Configure Workload Importance and how to Manage and monitor Workload Management.