Quickstart: Configure workload isolation in a dedicated SQL pool using T-SQL
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 free Azure account before you begin.
Creating a Synapse SQL instance in Azure Synapse Analytics may result in a new billable service. For more information, see Azure Synapse Analytics pricing.
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, "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.
- You've now created a workload group. Run a few queries as ELTLogin to see how they perform. See sys.dm_pdw_exec_requests to view queries and the workload group assigned.
- For more information about Synapse SQL workload management, see Workload Management and Workload Isolation.
Submit and view feedback for