Dedicated SQL Pool Performance issues

Question

We're utilizing Tableau's live connection feature, linked to a dedicated SQL pool. We've taken all necessary steps from the SQL pool side, such as creating required indexes, statistics, assigning appropriate distribution models and partitioning tables, enabling result set caching, materializing views, and so forth. When we execute the queries individually, as generated by Tableau, they complete in less than a second and rarely take more than 2 or 3 seconds in SSMS (with DWU 100)

However, when we run the same queries from Tableau, they take significantly longer to execute on the dedicated SQL pool, ranging from 15 to 45 seconds. Simultaneously, Tableau sends 15 requests to the dedicated SQL pool. We've noticed that several sessions are in a queued or suspended state during this time. We've experimented with scaling up the Data Warehouse Units (DWU) to 100, 200, and 400, but the issues persist even with increased DWU. As a result, users are not experiencing satisfactory performance from Tableau's end.

Can anyone please provide your inputs/thoughts on this and please let me know if you need any further details.

Thank you

Answer 1

Based on the information you shared, I will try to make some assumptions.

First thing to do, check the network latency between the Tableau server and the Azure Synapse environment. That is the 1st thing I would think about especially with live connections that are constantly querying data.

Second point, even though you've scaled your DWUs, the problem might lie in how Azure Synapse handles multiple concurrent queries. With Tableau sending 15 requests simultaneously, the resource contention might be high.

So in this case, you may need to adjust the resource classes for users and queries to ensure that more memory and compute resources are allocated to each query.

As a workaround to improve user experience, consider using Tableau extracts instead of live connections for reports that don't require real-time data.

Answer 2

Hi @Vinod Kumar Kapa

Adding to the above answer, To limit requests in batches from Tableau or Dedicated SQL pool, you can try to use Tableau's Data Engine to aggregate data before sending it to the dedicated SQL pool. This can help reduce the number of queries sent to the pool and improve performance.

Regarding caching table data, Azure Synapse Analytics provides a feature called "Materialized views" that can help improve query performance by precomputing and storing the results of a query. You can create a materialized view that contains the data needed for your report and then use it as the data source for your Tableau report. This can help reduce the number of queries sent to the dedicated SQL pool and improve performance.

Another option is to use Azure Synapse Analytics's "Result Set Caching" feature. This feature caches the results of a query in memory, allowing subsequent queries to retrieve the results from the cache instead of executing the query again. This can help improve query performance and reduce the load on the dedicated SQL pool.

Finally, you can explore using Azure Synapse Analytics's "Query Acceleration" feature. This feature uses machine learning to automatically optimize query performance by generating and maintaining statistics on the data in your dedicated SQL pool. This can help improve query performance and reduce the load on the dedicated SQL pool.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.