Azure SQL Data Warehouse performance questions

Posted bymartinistiPosted inAzure, Azure SQL Data WarehouseTags:

Scenario

I use Azure SQL Server Data Warehouse with x DWU but my queries are slow and according to the metrics the DWU usage is not even close to the limit like this:

What can I do? The old approach of throwing more resources (i.e. upscale to 2x DWU) simply would not work.

Investigation

Let’s run a chunky stored procedure that often takes longer to run than expected. While it’s running let’s query some DMVs:

SELECT   *
FROM     sys.dm_pdw_exec_requests
WHERE    end_time IS NULL
ORDER BY 1, 3, 4

It actually gives you a session_id as the first column that you can use to kill the process by executing

KILL 'session_id'

resource_class – that sounds like a good lead to follow! And yes if you check the official documentation (https://docs.microsoft.com/en-us/azure/sql-data-warehouse/resource-classes-for-workload-management) it tells you some important details about resource classes. The official definition:
“Resource classes are pre-determined resource limits in Azure SQL Data Warehouse that govern compute resources and concurrency for query execution.”

I haven’t found what 20 actually means in staticrc20 but my guess is that it limits the memory usage to 20% But how is it determined what resource_class is being used when a query is executed? These resource classes are actually database roles as well so if you find out what user context your query / stored procedure is running:

SELECT  w.[wait_id],
		w.[session_id],
		w.[type] AS Wait_type,
		w.[object_type],
		w.[object_name],
		w.[request_id],
		w.[request_time],
		w.[acquire_time],
		w.[state],
		w.[priority],
		SESSION_ID() AS Current_session,
		s.[status] AS Session_status,
		s.[login_name],
		s.[query_count],
		s.[client_id],
		s.[sql_spid],
		r.[command] AS Request_command,
		r.[label],
		r.[status] AS Request_status,
		r.[submit_time],
		r.[start_time],
		r.[end_compile_time],
		r.[end_time],
		DATEDIFF(ms, r.[submit_time], r.[start_time]) AS Request_queue_time_ms,
		DATEDIFF(ms, r.[start_time], r.[end_compile_time]) AS Request_compile_time_ms,
		DATEDIFF(ms, r.[end_compile_time], r.[end_time]) AS Request_execution_time_ms,
		r.[total_elapsed_time]
FROM    sys.dm_pdw_waits w
JOIN    sys.dm_pdw_exec_sessions s ON w.[session_id] = s.[session_id]
JOIN    sys.dm_pdw_exec_requests r ON w.[request_id] = r.[request_id]
WHERE    w.[session_id] <> SESSION_ID();

The [login_name] column contains that piece of information but the overall query can be useful.

So based on all this information my understanding is (there is not a lot of detailed information available about performance tuning Azure SQL Data Warehouse) that no matter how much DWU you allocate, the resource class actually caps how much resources a single operation can consume. This actually makes a lot of sense but only once you know about it and you’re aware of its implications. Because even if you run a single query in an SSMS window and the server itself is idle besides that your DWU usage is suboptimal. So there is a lot of room left for other, concurrent queries.

The question is how can you enforce higher resource utilisation for your monster query that produces a bottleneck?

Recommended action

Caveats and warnings

Unfortunately I have only been able to come up an all or nothing solution / workaround for automated ETL execution. The account used to run the ETL processes (that is usually a single account that is assigned at runtime from the orchestration engine) needs to be part of a different database role. The ones to choose from:

  • staticrc10 to staticrc80 (incrementing by 10)
  • dynamic resource classes: smallrc / mediumrc / largerc / xlargerc – from 3% memory limit to 70%

So you have nothing else to do that simply put the ETL service account into a different resource class database role. As a first step I’d recommend switching from staticrc20 to staticrc40. It would be create if Azure SQL Data Warehouse would have supported EXECUTE AS for stored procedures but it doesn’t. The feature development is in progress since Jan 2019 the latest but you can still upvote this: https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/14602404-support-execute-as-for-stored-procedures

Alternatively what you can do for testing is

  • Create a SQL user and assign it to e.g. the xlargerc dynamic resource class
  • Connect to your Azure SQL Data Warehouse server using that user
  • Run your query and test the results.

A sample SQL script for preparing this:


--CREATE local SQL user
--in master database
CREATE LOGIN [SvcDataLoader] WITH PASSWORD = 'your strong password here';

--in your Azure SQL Data Warehouse
CREATE USER [SvcDataLoader] FOR LOGIN [SvcDataLoader];

--assing user to a different rc role
EXEC sp_addrolemember 'xlargerc', 'SvcDataLoader'; --up to 70%
EXEC sp_addrolemember 'db_owner', 'SvcDataLoader'; --so that it can access the required tables

--check if user is assigned correctly
SELECT DP1.NAME AS DatabaseRoleName,
isnull(DP2.NAME, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id

WHERE DP1.type = 'R'
AND DP2.[name] = 'SvcDataLoader'
ORDER BY DP1.NAME;

Any questions, just let me know in the comments! Thanks for reading.

Leave a comment