快速入门:使用 T-SQL 在 Azure Synapse Analytics 中缩放专用 SQL 池(以前称为 SQL DW)中的计算Quickstart: Scale compute for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics using T-SQL

使用 T-SQL 和 SQL Server Management Studio (SSMS) 缩放专用 SQL 池(以前称为 SQL DW)中的计算。Scale compute in dedicated SQL pool (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 订阅,请在开始前创建一个试用帐户If you don't have an Azure subscription, create a 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).

创建专用 SQL 池(之前称为 SQL DW)Create a dedicated SQL pool (formerly SQL DW)

使用创建并连接 - 门户创建名为“mySampleDataWarehouse”的专用 SQL 池(之前称为 SQL DW)。Use Quickstart: create and Connect - portal to create a dedicated SQL pool (formerly SQL DW) named mySampleDataWarehouse. 完成此快速入门,以确保获得防火墙规则并可以从 SQL Server Management Studio 中连接到专用 SQL 池(之前称为 SQL DW)。Complete the quickstart to ensure you have a firewall rule and can connect to your dedicated SQL pool (formerly SQL DW) 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

服务目标设置包含专用 SQL 池(之前称为 SQL DW)的数据仓库单位数。The service objective setting contains the number of data warehouse units for the dedicated SQL pool (formerly SQL DW).

若要查看专用 SQL 池(之前称为 SQL DW)当前数据仓库单位:To view the current data warehouse units for your dedicated SQL pool (formerly SQL DW):

  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

在专用 SQL 池(之前称为 SQL DW)中,可以通过调整数据仓库单位来增加或减少计算资源。In dedicated SQL pool (formerly SQL DW), 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.

    操作状态

检查专用 SQL 池(之前称为 SQL DW)状态Check dedicated SQL pool (formerly SQL DW) state

暂停专用 SQL 池(之前称为 SQL DW)时,无法使用 T-SQL 连接它。When a dedicated SQL pool (formerly SQL DW) is paused, you can't connect to it with T-SQL. 若要查看专用 SQL 池(之前称为 SQL DW)的当前状态,可使用 PowerShell cmdlet。To see the current state of the dedicated SQL pool (formerly SQL DW), you can use a PowerShell cmdlet. 有关示例,请参阅检查专用 SQL 池(之前称为 SQL DW)状态 - PowerShellFor an example, see Check dedicated SQL pool (formerly SQL DW) state - PowerShell.

检查操作状态Check operation status

若要返回有关在专用 SQL 池(之前称为 SQL DW)的各种管理操作的信息,请运行以下查询 sys.dm_operation_status DMV。To return information about various management operations on your dedicated SQL pool (formerly SQL DW), 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

你现在已了解了如何缩放专用 SQL 池(之前称为 SQL DW)中的计算。You've now learned how to scale compute for your dedicated SQL pool (formerly SQL DW). 若要详细了解 Azure Synapse Analytics,请继续阅读有关加载数据的教程。To learn more about Azure Synapse Analytics, continue to the tutorial for loading data.