管理 Azure SQL 数据仓库中的计算资源Manage compute in Azure SQL Data Warehouse

了解如何管理 Azure SQL 数据仓库中的计算资源。Learn about managing compute resources in Azure SQL Data Warehouse. 可以通过暂停数据仓库来降低成本,或者根据性能需求缩放数据仓库。Lower costs by pausing the data warehouse, or scale the data warehouse to meet performance demands.

计算管理是什么?What is compute management?

SQL 数据仓库的体系结构对存储和计算功能进行了分隔,允许每项功能单独进行缩放。The architecture of SQL Data Warehouse separates storage and compute, allowing each to scale independently. 因此,可以独立于数据存储,根据性能需求缩放计算资源。As a result, you can scale compute to meet performance demands independent of data storage. 还可以暂停和恢复计算资源。You can also pause and resume compute resources. 此体系结构的自然结果是,计算和存储的计费是独立的。A natural consequence of this architecture is that billing for compute and storage is separate. 如果有一段时间不需要使用数据仓库,可以通过暂停计算来节省计算成本。If you don't need to use your data warehouse for a while, you can save compute costs by pausing compute.

缩放计算资源Scaling compute

调整数据仓库的数据仓库单位设置,可以横向扩展或还原计算资源。You can scale out or scale back compute by adjusting the data warehouse units setting for your data warehouse. 添加更多的数据仓库单位后,加载和查询性能可线性提高。Loading and query performance can increase linearly as you add more data warehouse units.

有关横向扩展的步骤,请参阅适用于 Azure 门户PowerShellT-SQL 的快速入门。For scale-out steps, see the Azure portal, PowerShell, or T-SQL quickstarts. 也可以使用 REST API 执行横向扩展操作。You can also perform scale-out operations with a REST API.

若要执行缩放操作,SQL 数据仓库首先会终止所有传入的查询,并会回滚事务以确保一致的状态。To perform a scale operation, SQL Data Warehouse first kills all incoming queries and then rolls back transactions to ensure a consistent state. 缩放只会在事务回滚完成后发生。Scaling only occurs once the transaction rollback is complete. 对于缩放操作,系统会从计算节点分离存储层、添加计算节点,然后将存储层重新附加到计算层。For a scale operation, the system detaches the storage layer from the Compute nodes, adds Compute nodes, and then reattaches the storage layer to the Compute layer. 每个数据仓库存储为在计算节点之间平均分布的 60 个分布区。Each data warehouse is stored as 60 distributions, which are evenly distributed to the Compute nodes. 添加更多计算节点可提高计算能力。Adding more Compute nodes adds more compute power. 随着计算节点的增加,每个计算节点的分布区数目会减少,因此可为查询提供更高的计算能力。As the number of Compute nodes increases, the number of distributions per compute node decreases, providing more compute power for your queries. 同样,减少数据仓库单位会减少计算节点数目,从而减少用于查询的计算资源。Likewise, decreasing data warehouse units reduces the number of Compute nodes, which reduces the compute resources for queries.

下表显示了当数据仓库单位数发生变化时,每个计算节点的分布区数目如何变化。The following table shows how the number of distributions per Compute node changes as the data warehouse units change. DWU6000 提供 60 个计算节点,实现的查询性能比 DWU100 高得多。DWU6000 provides 60 Compute nodes and achieves much higher query performance than DWU100.

数据仓库单位数Data warehouse units 计算节点数 ## of Compute nodes 每个节点的分布区 ## of distributions per node
100100 11 6060
200200 22 3030
300300 33 20 个20
400400 44 1515
500500 55 1212
600600 66 10 个10
10001000 10 个10 66
12001200 1212 55
15001500 1515 44
20002000 20 个20 33
30003000 3030 22
60006000 6060 11

找到数据仓库单位的适当大小Finding the right size of data warehouse units

若要体验横向扩展带来的性能优势(尤其是对于较大的数据仓库单位),可以至少使用 1 TB 的数据集。To see the performance benefits of scaling out, especially for larger data warehouse units, you want to use at least a 1-TB data set. 若要找到数据仓库的最佳数据仓库单位数目,请尝试纵向扩展和缩减。To find the best number of data warehouse units for your data warehouse, try scaling up and down. 加载数据后,使用不同数量的数据仓库单位运行几个查询。Run a few queries with different numbers of data warehouse units after loading your data. 由于缩放很快就能完成,可以在一个小时或更少时间内尝试一些不同级别的性能。Since scaling is quick, you can try various performance levels in an hour or less.

有关找到最佳数据仓库单位数目的建议:Recommendations for finding the best number of data warehouse units:

  • 对于正在开发的数据仓库,可以从少量的数据仓库单位开始。For a data warehouse in development, begin by selecting a smaller number of data warehouse units. 一个好的起点为 DW400 或 DW200。A good starting point is DW400 or DW200.
  • 监视应用程序性能,将所选数据仓库单位数目与观测到的性能变化进行比较。Monitor your application performance, observing the number of data warehouse units selected compared to the performance you observe.
  • 采用线性缩放,确定需要以多大的增量来增加或减少数据仓库单位。Assume a linear scale, and determine how much you need to increase or decrease the data warehouse units.
  • 继续进行调整,直到达到业务要求的最佳性能级别。Continue making adjustments until you reach an optimum performance level for your business requirements.

何时横向扩展When to scale out

横向扩展数据仓库单位会影响性能的以下方面:Scaling out data warehouse units impacts these aspects of performance:

  • 以线性方式改善系统的扫描、聚合和 CTAS 语句性能。Linearly improves performance of the system for scans, aggregations, and CTAS statements.
  • 增加用于加载数据的读取器和编写器数量。Increases the number of readers and writers for loading data.
  • 并发查询和并发槽的最大数量。Maximum number of concurrent queries and concurrency slots.

有关何时横向扩展数据仓库单位的建议:Recommendations for when to scale out data warehouse units:

  • 执行繁重的数据加载或转换操作时,可以横向扩展以更快速地获取数据。Before you perform a heavy data loading or transformation operation, scale out to make the data available more quickly.
  • 高峰业务时段,可以横向扩展以容纳更大数量的并发查询。During peak business hours, scale out to accommodate larger numbers of concurrent queries.

如果横向扩展无法提高性能怎么办?What if scaling out does not improve performance?

添加数据仓库单位可提高并行度。Adding data warehouse units increasing the parallelism. 如果工作在计算节点之间均匀拆分,则更高的并行度可以提高查询性能。If the work is evenly split between the Compute nodes, the additional parallelism improves query performance. 有几种原因会导致横向扩展不能改变性能。If scaling out is not changing your performance, there are some reasons why this might happen. 数据可能在分布区中扭曲,或者查询可能引入了大量的数据移动操作。Your data might be skewed across the distributions, or queries might be introducing a large amount of data movement. 若要调查查询性能问题,请参阅排查性能问题To investigate query performance issues, see Performance troubleshooting.

暂停和恢复计算资源Pausing and resuming compute

暂停计算资源会导致存储层从计算节点分离。Pausing compute causes the storage layer to detach from the Compute nodes. 将从帐户中释放计算资源。The Compute resources are released from your account. 暂停计算资源时,不会产生计算费用。You are not charged for compute while compute is paused. 恢复计算资源会将存储重新附加到计算节点,并且恢复计算费用。Resuming compute reattaches storage to the Compute nodes, and resumes charges for Compute. 暂停数据仓库时:When you pause a data warehouse:

  • 计算和内存资源返回到数据中心的可用资源池中Compute and memory resources are returned to the pool of available resources in the data center
  • 暂停期间,数据仓库单位的费用为零。Data warehouse unit costs are zero for the duration of the pause.
  • 不影响数据存储,数据保持不变。Data storage is not affected and your data stays intact.
  • SQL 数据仓库将取消所有正在运行或已排队的操作。SQL Data Warehouse cancels all running or queued operations.

恢复数据仓库时:When you resume a data warehouse:

  • SQL 数据仓库将获取数据仓库单位设置的计算和内存资源。SQL Data Warehouse acquires compute and memory resources for your data warehouse units setting.
  • 数据仓库单位的计算费用将会恢复。Compute charges for your data warehouse units resume.
  • 数据可用。Your data becomes available.
  • 数据仓库联机后,需要重启工作负荷查询。After the data warehouse is online, you need to restart your workload queries.

如果希望随时可访问数据仓库,请考虑将其缩减到最小大小,而不是暂停。If you always want your data warehouse accessible, consider scaling it down to the smallest size rather than pausing.

有关暂停和恢复步骤,请参阅适用于 Azure 门户PowerShell 的快速入门。For pause and resume steps, see the Azure portal, or PowerShell quickstarts. 也可以使用暂停 REST API恢复 REST APIYou can also use the pause REST API or the resume REST API.

暂停或缩放之前清空事务Drain transactions before pausing or scaling

在启动暂停或缩放操作之前,我们建议先让现有事务完成。We recommend allowing existing transactions to finish before you initiate a pause or scale operation.

在暂停或缩放 SQL 数据仓库时,用户一发起暂停或缩放请求,系统就会在后台取消查询。When you pause or scale your SQL Data Warehouse, behind the scenes your queries are canceled when you initiate the pause or scale request. 取消简单的 SELECT 查询是很快的操作,对于暂停或缩放实例所花费的时间几乎没有什么影响。Canceling a simple SELECT query is a quick operation and has almost no impact to the time it takes to pause or scale your instance. 但是,事务性查询(会修改数据或结构)可能无法快速地停止。However, transactional queries, which modify your data or the structure of the data, may not be able to stop quickly. 按定义,事务性查询必须完全完成或回退更改。Transactional queries, by definition, must either complete in their entirety or rollback their changes. 回滚事务性查询已完成的任务可能需要很长时间,甚至比查询应用原始更改更久。Rolling back the work completed by a transactional query can take as long, or even longer, than the original change the query was applying. 例如,如果取消的删除行查询已经运行一小时,系统可能需要一个小时重新插入已删除的行。For example, if you cancel a query which was deleting rows and has already been running for an hour, it could take the system an hour to insert back the rows which were deleted. 如果在事务运行中运行暂停或缩放,暂停或缩放操作可能需要一些时间,因为暂停和缩放必须等回滚完成才能继续。If you run pause or scaling while transactions are in flight, your pause or scaling may seem to take a long time because pausing and scaling has to wait for the rollback to complete before it can proceed.

另请参阅了解事务优化事务See also Understanding transactions, and Optimizing transactions.

权限Permissions

缩放数据仓库需要 ALTER DATABASE 中所述的权限。Scaling the data warehouse requires the permissions described in ALTER DATABASE. 暂停和恢复需要 SQL DB 参与者权限,具体而言是 Microsoft.Sql/servers/databases/action 权限。Pause and Resume require the SQL DB Contributor permission, specifically Microsoft.Sql/servers/databases/action.

后续步骤Next steps

计算资源管理工作的另一方面是为单个查询分配不同的计算资源。Another aspect of managing compute resources is allocating different compute resources for individual queries. 有关详细信息,请参阅用于工作负荷管理的资源类For more information, see Resource classes for workload management.