Quickstart: Scale compute for dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics using T-SQL

  • Commodity
  • 3 minutes to read

Scale compute in dedicated SQL pool (formerly SQL DW) using T-SQL and SQL Server Management Studio (SSMS). Scale out compute for better operation, or scale dorsum compute to relieve costs.

If you don't have an Azure subscription, create a free account before y'all brainstorm.

Before you brainstorm

Download and install the newest version of SQL Server Management Studio (SSMS).

Create a defended SQL pool (formerly SQL DW)

Use Quickstart: create and Connect - portal to create a dedicated SQL pool (formerly SQL DW) named mySampleDataWarehouse. Complete the quickstart to ensure you have a firewall dominion and tin connect to your defended SQL pool (formerly SQL DW) from within SQL Server Management Studio.

Connect to the server as server admin

This section uses SQL Server Management Studio (SSMS) to constitute a connectedness to your Azure SQL server.

  1. Open SQL Server Management Studio.

  2. In the Connect to Server dialog box, enter the post-obit information:

    Setting Suggested value Description
    Server type Database engine This value is required
    Server name The fully qualified server proper name Here's an example: mySampleDataWarehouseservername.database.windows.net.
    Hallmark SQL Server Authentication SQL Authentication is the simply 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 yous created the server.

    Connect to server

  3. Click Connect. The Object Explorer window opens in SSMS.

  4. In Object Explorer, expand Databases. And then expand mySampleDataWarehouse to view the objects in your new database.

    Database objects

View service objective

The service objective setting contains the number of data warehouse units for the dedicated SQL pool (formerly SQL DW).

To view the electric current data warehouse units for your defended SQL pool (formerly SQL DW):

  1. Under the connection to mySampleDataWarehouseservername.database.windows.net, aggrandize Organization Databases.

  2. Correct-click master and select New Query. A new query window opens.

  3. 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 Bring together     sys.databases db ON ds.database_id = db.database_id WHERE     db.proper name = 'mySampleDataWarehouse'

  4. The following results show mySampleDataWarehouse has a service objective of DW400.

    iew-current-dwu

Scale compute

In dedicated SQL pool (formerly SQL DW), you can increase or decrease compute resource by adjusting data warehouse units. The Create and Connect - portal created mySampleDataWarehouse and initialized it with 400 DWUs. The following steps adapt the DWUs for mySampleDataWarehouse.

To modify data warehouse units:

  1. Right-click master and select New Query.

  2. Use the ALTER DATABASE T-SQL statement to modify the service objective. Run the post-obit query to alter the service objective to DW300.

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

Monitor scale change request

To see the progress of the previous change request, you can apply 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. Right-click main and select New Query.

  2. 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 functioning = 'ALTER DATABASE'     Lodge BY         start_time DESC ) = 'IN_PROGRESS' BEGIN     RAISERROR('Calibration operation in progress',0,0) WITH NOWAIT;     WAITFOR DELAY '00:00:05'; Finish Print 'Consummate';

  3. The resulting output shows a log of the polling of the status.

    Operation status

Check defended SQL pool (formerly SQL DW) state

When a dedicated SQL pool (formerly SQL DW) is paused, yous tin't connect to it with T-SQL. To encounter the current state of the defended SQL puddle (formerly SQL DW), you lot can use a PowerShell cmdlet. For an example, see Cheque dedicated SQL pool (formerly SQL DW) country - PowerShell.

Bank check operation status

To return data about various management operations on your dedicated SQL pool (formerly SQL DW), run the following query on the sys.dm_operation_status DMV. For example, it returns the operation and the state of the performance, 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 lot've now learned how to scale compute for your defended SQL pool (formerly SQL DW). To learn more about Azure Synapse Analytics, keep to the tutorial for loading data.