Synapse SQL resource consumption in Dedicated/ Serverless SQL pool

Posted by

For Serverless SQL pool

  • Serverless SQL pool is a pay per query service that doesn’t require you to pick the right size.
  • The system automatically adjusts based on your requirements, freeing you up from managing your infrastructure and picking the right size for your solution.

For Dedicated SQL pool – Data Warehouse Units (DWUs) and compute Data Warehouse Units (cDWUs)

The Recommendations on choosing the ideal number of data warehouse units (DWUs) to optimize price and performance, and how to change the number of units.

What is Data Warehouse Units?

  • A Synapse SQL pool represents a collection of analytic resources 
  • Analytic resources are defined as a combination of CPU, memory, and IO.These three resources are bundled into units of compute scale called Data Warehouse Units (DWUs).
  • For higher performance, you can increase the number of data warehouse units. For less performance, reduce data warehouse units. 
  •  Storage and compute costs are billed separately, so changing data warehouse units does not affect storage costs.

Increasing DWUs:

  • Linearly changes performance of the system for scans, aggregations, and CTAS statements
  • Increases the number of readers and writers for PolyBase load operations
  • Increases the maximum number of concurrent queries and concurrency slots.

Service Level Objective

The Service Level Objective (SLO) is the scalability setting that determines the cost and performance level of your data warehouse. 

  • The service levels for Gen2 are measured in compute data warehouse units (cDWU), for example DW2000c.
  • Gen1 service levels are measured in DWUs, for example DW2000.

Note: Azure Synapse Analytics Gen2 recently added additional scale capabilities to support compute tiers as low as 100 cDWU. Existing data warehouses currently on Gen1 that require the lower compute tiers can now upgrade to Gen2 in the regions that are currently available for no additional cost. If your region is not yet supported, you can still upgrade to a supported region

In T-SQL, the SERVICE_OBJECTIVE setting determines the service level and the performance tier for your dedicated SQL pool.

CREATE DATABASE mySQLDW
(Edition = 'Datawarehouse'
 ,SERVICE_OBJECTIVE = 'DW1000c'
)
;

What is Performance Tiers and Data Warehouse Units?

  • Gen1 data warehouses are measured in Data Warehouse Units (DWUs).
  • Gen2 data warehouses are measured in compute Data Warehouse Units (cDWUs).

Both DWUs and cDWUs support scaling compute up or down, and pausing compute when you don’t need to use the data warehouse. Gen2 uses a local disk-based cache on the compute nodes to improve performance. Gen2 provides the best query performance and highest scale. Gen2 systems also make the most use of the cache.

Each SQL server (for example, myserver.database.windows.net) has a Database Transaction Unit (DTU) quota that allows a specific number of data warehouse units.

How to Assess the number of data warehouse units needed

The ideal number of data warehouse units depends very much on your workload and the amount of data you’ve loaded into the system.

Below are steps for finding the best DWU for your workload:

  • Begin by selecting a smaller DWU.
  • Monitor your application performance as you test data loads into the system, observing the number of DWUs selected compared to the performance you observe.
  • Identify any additional requirements for periodic periods of peak activity.

Note : Query performance only increases with more parallelization if the work can be split between compute nodes. If you find that scaling is not changing your performance, you may need to tune your table design and/or your queries. 

How to change DWU settings

Azure built-in roles such as SQL DB Contributor and SQL Server Contributor can change DWU settings

Azure portal

To change DWUs:

  • Open the Azure portal, open your database, and select Scale.
  • Under Scale, move the slider left or right to change the DWU setting.
  • Select Save. A confirmation message appears. Select yes to confirm or no to cancel.

PowerShell

Set-AzSqlDatabase -DatabaseName "MySQLDW" -ServerName "MyServer" -RequestedServiceObjectiveName "DW1000c"

T-SQL

With T-SQL you can view the current DWUsettings, change the settings, and check the progress.

For Dedicated pool

ALTER DATABASE { database_name | CURRENT }
{
  MODIFY NAME = new_database_name
| MODIFY ( <edition_option> [, ... n] )
| SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<edition_option> ::=
      MAXSIZE = {
            250 | 500 | 750 | 1024 | 5120 | 10240 | 20480
          | 30720 | 40960 | 51200 | 61440 | 71680 | 81920
          | 92160 | 102400 | 153600 | 204800 | 245760
      } GB
      | SERVICE_OBJECTIVE = {
            'DW100' | 'DW200' | 'DW300' | 'DW400' | 'DW500'
          | 'DW600' | 'DW1000' | 'DW1200' | 'DW1500' | 'DW2000'
          | 'DW3000' | 'DW6000' | 'DW500c' | 'DW1000c' | 'DW1500c'
          | 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c' | 'DW6000c'
          | 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
      }

For Serverless pool

ALTER DATABASE { database_name | Current } 
{ 
    COLLATE collation_name 
  | SET { <optionspec> [ ,...n ] } 
} 
[;] 

<optionspec> ::= 
{ 
    <auto_option> 
  | <sql_option> 
}  

<auto_option> ::= 
{ 
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] } 
} 

<sql_option> ::= 
{ 
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 140 | 130 | 120 | 110 | 100 } 
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
}

How to View current DWU settings

To view the current DWU setting:

  1. Open SQL Server Object Explorer in Visual Studio.
  2. Connect to the master database associated with the logical SQL server.
  3. Select from the sys.database_service_objectives dynamic management view. Here is an example:
SELECT  db.name [Database]
,        ds.edition [Edition]
,        ds.service_objective [Service Objective]
FROM    sys.database_service_objectives   AS ds
JOIN    sys.databases                     AS db ON ds.database_id = db.database_id
;

Note:

When you start a scale operation, the system first kills all open sessions, rolling back any open transactions to ensure a consistent state. 

  • For a scale-up operation, the system detaches all compute nodes, provisions the additional compute nodes, and then reattaches to the storage layer.
  • For a scale-down operation, the system detaches all compute nodes and then reattaches only the needed nodes to the storage layer.
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x