Quickstart: Configure workload isolation in a dedicated SQL pool using T-SQL

  • Article

In this quickstart, you'll quickly create a workload group and classifier for reserving resources for data loading. The workload group will allocate 20% of the system resources to the data loads. The workload classifier will assign requests to the data loads workload group. With 20% isolation for data loads, they are guaranteed resources to hit SLAs.

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

Note

Creating a Synapse SQL instance in Azure Synapse Analytics may result in a new billable service. For more information, see Azure Synapse Analytics pricing.

Prerequisites

This quickstart assumes you already have a Synapse SQL instance in Azure Synapse 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.

Create login for DataLoads

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

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

Create user

Create user, "ELTLogin", in mySampleDataWarehouse

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

Create a workload group

Create a workload group for DataLoads with 20% isolation.

CREATE WORKLOAD GROUP DataLoads
WITH ( MIN_PERCENTAGE_RESOURCE = 20
      ,CAP_PERCENTAGE_RESOURCE = 100
      ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 5)
;

Create a workload classifier

Create a workload classifier to map ELTLogin to the DataLoads workload group.

CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
WITH (WORKLOAD_GROUP = 'DataLoads'
      ,MEMBERNAME = 'ELTLogin')
;

View existing workload groups and classifiers and run-time values

--Workload groups
SELECT * FROM
sys.workload_management_workload_groups

--Workload classifiers
SELECT * FROM
sys.workload_management_workload_classifiers

--Run-time values
SELECT * FROM
sys.dm_workload_management_workload_groups_stats

Clean up resources

DROP WORKLOAD CLASSIFIER [wgcELTLogin]
DROP WORKLOAD GROUP [DataLoads]
DROP USER [ELTLogin]
;

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.

Next steps