快速入门:使用 T-SQL 配置工作负荷隔离Quickstart: Configure workload isolation using T-SQL

在本快速入门中,你将快速创建工作负荷组和分类器,以便保留资源以进行数据加载。In this quickstart, you'll quickly create a workload group and classifier for reserving resources for data loading. 工作负荷组将向数据加载分配 20% 的系统资源。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. 它们对数据加载实现 20% 的隔离,这两项资源都必定满足 SLA 要求。With 20% isolation for data loads, they are guaranteed resources to hit SLAs.

如果没有 Azure 订阅,可在开始前创建一个 1 元人民币试用帐户。If you don't have an Azure subscription, create a 1rmb trial account before you begin.

备注

在 Azure Synapse Analytics 中创建一个 Synapse SQL 实例可能会产生一个新的可计费服务。Creating a Synapse SQL instance in Azure Synapse Analytics may result in a new billable service. 有关详细信息,请参阅 Azure Synapse Analytics 定价For more information, see Azure Synapse Analytics pricing.

先决条件Prerequisites

本快速入门假定你已在 Azure Synapse 中有 Synapse SQL 实例,并且具有 CONTROL DATABASE 权限。This quickstart assumes you already have a Synapse SQL instance in Azure Synapse and that you have CONTROL DATABASE permissions. 如果需要创建一个 SQL 数据仓库,可使用创建并连接 - 门户创建名为“mySampleDataWarehouse”的数据仓库。If you need to create one, use Create and Connect - portal to create a data warehouse called mySampleDataWarehouse.

为 DataLoads 创建登录名Create login for DataLoads

使用 CREATE LOGINmaster 数据库中为“ELTLogin”创建 SQL Server 身份验证登录名。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

在 mySampleDataWarehouse 中创建用户“ELTLogin”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

为隔离度为 20% 的 DataLoads 创建工作负荷组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

创建工作负荷分类器以将 ELTLogin 映射到 DataLoads 工作负荷组。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 data warehouse. 这些计算和存储资源是分开计费的。These compute and storage resources are billed separately.

  • 如果想要将数据保留在存储中,可以在不使用 SQL 池时暂停计算。If you want to keep the data in storage, you can pause compute when you aren't using the 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 data warehouse.

后续步骤Next steps