Long running prepare statements on Azure SQL Data Warehouse

0
votes

I am running a functional test of a 3rd party application on an Azure SQL Data Warehouse database set at DWU 1000. In reviewing the current activity via: 

sys.dm_pdw_exec_requests

I see:

  • prepare statements taking 30+ seconds,
  • NULL statements taking up to 25 seconds,
  • compilation of statements takes up to 60 seconds,
  • explain statements taking 60+ seconds, and
  • select count(1) from empty tables take 60+ seconds.

How does one identify the bottleneck involved?

The test has been running for a few hours and the Azure portal shows little DWU consumed on average, so I doubt that modifying the DWU will make any difference.

The third-party application has workload management feature, so I've specified a limit of 30 connections to the ADW database (understanding that only 32 sessions are active on the database itself.)

There are approximately ~1,800 tables and ~350 views in the database across 29 schemas (per information_schema.tables).

I am in a functional testing mode, so many of the tables involved in the queries have not yet been loaded, but statistics have been created on every column on every table in the scope of the test.

One userID is being used in the test. It is in smallrc.

1
azure-sqldw
Post the SQL statement, the explain plan, the DDL of the tables involved and their rowcounts. Anonymise it if you need to. - wBob
The DMV only shows command of: exec [sp_prepare] \@P1 OUT, \@P2, \@P3, \@P4" and a few other permutations. - Steve
Can you give a fuller description of your test? For example are you attempting to run large numbers of concurrent users? As you know, Azure SQL Data Warehouse can only have 32 concurrent queries but this is lower when using larger resource classes, e.g. mediumrc, largerc, xlargerc. Queries will then queue. Raising DWU will help if this is the issue. - wBob
One other observation is, the userID used in the test is in small_rc. At DWU 1000, that should allow the full 32 active sessions with each session using one slot of the 40 available concurrency slots. When I look at sys.dm_pdw_exec_requests I only see 5-10 requests with a non-null resource_class. - Steve
Yes that's true. Thanks for getting that info, hopefully you can see why I asked for it. Consider opening a support case via the portal, report back any findings to this thread. - wBob

1 Answers

1
votes

have a look at your tables - in the query? Make sure all columns in joins, group by, and order by have up-to-date statistics.

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-statistics