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 free account before you begin.
Creating a dedicated SQL pool instance in Azure Synapse Analytics may result in a new billable service. For more information, see Azure Synapse Analytics pricing.
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
Sign in to the Azure portal.
Create login for TheCEO
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
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);
View existing classifiers
SELECT * FROM sys.workload_management_workload_classifiers
Clean up resources
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.
Sign in to the Azure portal, select your dedicated SQL pool.
To pause compute, select the Pause button. When the dedicated SQL pool is paused, you see a Start button. To resume compute, select Start.
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.