0

I have a question on how exactly resource class and concurrency work in ADW. I have configured a user account as staticrc30 (4 concurrency slots) and I am using this account from Tableau dashboard. My dashboard makes total of 12 queries to ADW. I am running ADW at 400 DWUs which means it has 16 concurrency slots.Does all my 12 queries run in sequence OR they run in batches of 4? Even though each of my SQL Queries is taking less time overall dashboard rendering time is very long.

Dataman
  • 27
  • 1
  • 6

1 Answers1

2

You should be seeing four queries running concurrently, assuming nothing else is running on the DW at the time.

You can check this using the waiting queries DMV example, shown here:

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-waiting-queries

If you're not seeing four running, think about whether your Tableau desktop has been configured to run parallel queries:

https://kb.tableau.com/articles/howto/Configuring-Parallel-Queries-in-Tableau-Desktop

Ron Dunn
  • 2,971
  • 20
  • 27
  • Great , Thanks Ron that was helpful. Another question on the same lines, in ADW Gen2 when does the Caching in NVMe happens? Will that happen on a scheduled basis like nightly? I ask this because some of the queries which were taking time yesterday have become fast today without me doing any change. – Dataman Jun 10 '18 at 17:56
  • I've asked the product team for some deeper details of the cache process, I'll update this message as soon as I get their response. – Ron Dunn Jun 11 '18 at 02:11
  • Sure, Thank you! – Dataman Jun 11 '18 at 04:07
  • The cache is continuously maintained on an LRU basis, there is no periodic collection of data. Did you run any ELT processes overnight? Was data added to your tables? The cache only stores the clustered columnstore columnar segments, not the delta store. If you changed the data, you might get a different CC index structure that performed better. Here's some further documentation. https://azure.microsoft.com/en-us/blog/adaptive-caching-powers-azure-sql-data-warehouse-performance-gains/ – Ron Dunn Jun 11 '18 at 04:12
  • No, there was no ELT process which ran overnight. Is there a statement similar to SET IO STATISTICS ON in ADW? A way to know whether data is pulled from Cache or remote IO? – Dataman Jun 13 '18 at 16:00
  • If you paused your DW the cache will be progressively rebuilt as you run queries after restarting. There is no SET statement of that type. Please post the EXPLAIN plan for the query. – Ron Dunn Jun 14 '18 at 01:21