快速入门:使用 T-SQL 缩放 Azure Synapse Analytics 中的计算Quickstart: Scale compute in Azure Synapse Analytics using T-SQL

使用 T-SQL 和 SQL Server Management Studio (SSMS) 缩放 Azure Synapse Analytics(以前称为 SQL DW)中的计算。Scale compute in Azure Synapse Analytics (formerly SQL DW) using T-SQL and SQL Server Management Studio (SSMS). 横向扩展计算以提高性能或按比例缩减计算以节约成本。Scale out compute for better performance, or scale back compute to save costs.

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

准备阶段Before you begin

下载并安装最新版本的 SQL Server Management Studio (SSMS)。Download and install the newest version of SQL Server Management Studio (SSMS).

创建数据仓库Create a data warehouse

使用创建并连接 - 门户创建名为“mySampleDataWarehouse”的数据仓库 。Use Quickstart: create and Connect - portal to create a data warehouse named mySampleDataWarehouse. 完成此快速入门,以确保获得防火墙规则并可以从 SQL Server Management Studio 中连接到数据仓库。Complete the quickstart to ensure you have a firewall rule and can connect to your data warehouse from within SQL Server Management Studio.

以服务器管理员的身份连接到服务器Connect to the server as server admin

本部分使用 SQL Server Management Studio (SSMS) 来建立与 Azure SQL Server 的连接。This section uses SQL Server Management Studio (SSMS) to establish a connection to your Azure SQL server.

  1. 打开 SQL Server Management Studio。Open SQL Server Management Studio.

  2. 在“连接到服务器”对话框中,输入以下信息: In the Connect to Server dialog box, enter the following information:

    设置Setting       建议的值Suggested value 说明Description
    服务器类型Server type 数据库引擎Database engine 此值是必需的This value is required
    服务器名称Server name 完全限定的服务器名称The fully qualified server name 这里有一个示例:mySampleDataWarehouseservername.database.chinacloudapi.cn 。Here's an example: mySampleDataWarehouseservername.database.chinacloudapi.cn.
    身份验证Authentication SQL Server 身份验证SQL Server Authentication SQL 身份验证是本教程中配置的唯一身份验证类型。SQL Authentication is the only authentication type that is configured in this tutorial.
    登录Login 服务器管理员帐户The server admin account 在创建服务器时指定的帐户。The account that you specified when you created the server.
    密码Password 服务器管理员帐户的密码The password for your server admin account 创建服务器时指定的密码。The password you specified when you created the server.

    连接到服务器

  3. 单击“连接” 。Click Connect. 此时会在 SSMS 中打开“对象资源管理器”窗口。The Object Explorer window opens in SSMS.

  4. 在“对象资源管理器”中,展开“数据库” 。In Object Explorer, expand Databases. 然后展开“mySampleDataWarehouse” ,查看新数据库中的对象。Then expand mySampleDataWarehouse to view the objects in your new database.

    数据库对象

查看服务目标View service objective

服务目标设置包含的数据仓库的数据仓库单位数。The service objective setting contains the number of data warehouse units for the data warehouse.

若要查看数据仓库当前数据仓库单位:To view the current data warehouse units for your data warehouse:

  1. 在与“mySampleDataWarehouseservername.database.chinacloudapi.cn” 的连接下,展开“系统数据库” 。Under the connection to mySampleDataWarehouseservername.database.chinacloudapi.cn, expand System Databases.

  2. 右键单击“master”,并单击“新建查询”。 Right-click master and select New Query. 此时将打开一个新的查询窗口。A new query window opens.

  3. 运行以下查询,从 sys.database_service_objectives 动态管理视图中选择。Run the following query to select from the sys.database_service_objectives dynamic management view.

    SELECT
        db.name [Database]
    ,    ds.edition [Edition]
    ,    ds.service_objective [Service Objective]
    FROM
         sys.database_service_objectives ds
    JOIN
        sys.databases db ON ds.database_id = db.database_id
    WHERE
        db.name = 'mySampleDataWarehouse'
    
  4. 以下结果显示 mySampleDataWarehouse 具有一个 DW400 的服务目标。The following results show mySampleDataWarehouse has a service objective of DW400.

    iew-current-dwu

缩放计算Scale compute

在 Azure Synapse 中,可以通过调整数据仓库单位来增加或减少计算资源。In Azure Synapse, you can increase or decrease compute resources by adjusting data warehouse units. 创建和 Connect - 门户创建 mySampleDataWarehouse 并初始化 400 DWU。The Create and Connect - portal created mySampleDataWarehouse and initialized it with 400 DWUs. 以下步骤调整为 DWU mySampleDataWarehouseThe following steps adjust the DWUs for mySampleDataWarehouse.

更改数据仓库单位:To change data warehouse units:

  1. 右键单击“master”,并单击“新建查询”。 Right-click master and select New Query.

  2. 使用 ALTER DATABASE T-SQL 语句修改的服务目标。Use the ALTER DATABASE T-SQL statement to modify the service objective. 运行以下查询以将服务目标更改为 DW300。Run the following query to change the service objective to DW300.

    ALTER DATABASE mySampleDataWarehouse
    MODIFY (SERVICE_OBJECTIVE = 'DW300c');
    

监视规模更改请求Monitor scale change request

若要查看以前的更改请求的进度,可以使用 WAITFORDELAY T-SQL 语法来轮询 sys.dm_operation_status 动态管理视图 (DMV)。To see the progress of the previous change request, you can use the WAITFORDELAY T-SQL syntax to poll the sys.dm_operation_status dynamic management view (DMV).

若要轮询服务对象更改状态,请执行以下操作:To poll for the service object change status:

  1. 右键单击“master”,并单击“新建查询”。 Right-click master and select New Query.

  2. 运行以下查询来轮询 sys.dm_operation_status DMV。Run the following query to poll the sys.dm_operation_status DMV.

    WHILE
    (
        SELECT TOP 1 state_desc
        FROM sys.dm_operation_status
        WHERE
            1=1
            AND resource_type_desc = 'Database'
            AND major_resource_id = 'mySampleDataWarehouse'
            AND operation = 'ALTER DATABASE'
        ORDER BY
            start_time DESC
    ) = 'IN_PROGRESS'
    BEGIN
        RAISERROR('Scale operation in progress',0,0) WITH NOWAIT;
        WAITFOR DELAY '00:00:05';
    END
    PRINT 'Complete';
    
  3. 生成的输出显示了状态轮询的日志。The resulting output shows a log of the polling of the status.

    操作状态

检查数据仓库状态Check data warehouse state

暂停数据仓库后,无法使用 T-SQL 与它建立连接。When a data warehouse is paused, you can't connect to it with T-SQL. 若要查看数据仓库的当前状态,可以使用 PowerShell cmdlet。To see the current state of the data warehouse, you can use a PowerShell cmdlet. 有关示例,请参阅检查数据仓库状态 - PowerShellFor an example, see Check data warehouse state - Powershell.

检查操作状态Check operation status

若要返回有关 Azure Synapse 上各种管理操作的信息,请针对 sys.dm_operation_status DMV 运行以下查询。To return information about various management operations on your Azure Synapse, run the following query on the sys.dm_operation_status DMV. 例如,它返回操作和操作状态,后者为 IN_PROGRESS 或 COMPLETED。For example, it returns the operation and the state of the operation, which is IN_PROGRESS or COMPLETED.

SELECT *
FROM
    sys.dm_operation_status
WHERE
    resource_type_desc = 'Database'
AND
    major_resource_id = 'mySampleDataWarehouse'

后续步骤Next steps

你现在已了解如何缩放数据仓库的计算。You've now learned how to scale compute for your data warehouse. 若要详细了解 Azure Synapse,请继续阅读有关加载数据的教程。To learn more about Azure Synapse, continue to the tutorial for loading data.