How to determine the DWU setting for a SQL Data Warehouse

  • Article

Azure SQL Data Warehouse service allows you to dynamically size the amount of compute resources within seconds. The setting for the amount of DWU (Data Warehouse Units = compute power) that is assigned can be viewed by opening the Azure Portal and viewing the database blade:

The SQL team has now introduced a new DMV (sys.database_service_objectives) that allows you to programmatically access the DWU setting. You will need to connect to the master database of your logical server and you can run this query:

 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

 

 This will return all of the databases and their Service Objective (the column that contains the DWU setting) on the logical server.


 In this sample, you can see a logical server that contains 3 Data Warehouse and 1 Azure SQL Database databases. You can also filter for just SQL Data Warehouses:

 

 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
 ds.edition = 'DataWarehouse'

 

To learn more about Azure SQL Data Warehouse, visit the main product page.