3

I'm trying to tune a DB in Azure SQL Datawarehouse for queries produced by Power BI, which can connect directly to Azure SQL Data warehouse. I'm interested in studying execution plans, tweak things like indexes and statistics, retry queries to test changes and so on. A typical way to do this is by looking at sys.dm_pdw_exec_requests system view to get the execution times of queries, their text and their ID, in order to then study their steps with sys.dm_pdw_request_steps. Some of these queries are longer than 4000 characters because of the typical automated query generation syntactic clutter.

Microsoft documentation states that "command" column holds up to 4000 characters, so any query bigger than that is truncated.

... So I cannot study a query longer than that

I have not been able to find a way to get the queries yet. Any ideas?

1

This came up the other day and the reply was "switch on Auditing":

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-auditing-overview

I have not tried this myself yet so I'm not sure if you get the full text of the query. I plan to test this feature in the next few weeks so will update the post, or you can : )

I have been having similar issues with Power BI queries which seem overly complicated, but I suspect this is a consequence of an overly complicated Power BI model. One alternative I have thought of (but not yet done) is to create a schema-only model of the database in SQL Server 2016. You could accurately reflect the columnstore, heap or clustered index and partitioning index choices, but not the distribution and data movement aspects. This would at least give you access to tools like Extended Events (or Profiler if you're old-school), visual Execution Plans (although you can you EXPLAIN plans), bearing in mind the database engines are different. I am yet to determine if the effort of creating and maintaining the copy is worth it, but I will update if I progress my thinking on this.

This was also discussed here:

https://stackoverflow.com/questions/41292662/identifying-needed-statistics-azure-sql-data-warehouse

  • 1
    To me there's an intrinsic problem with Power BI integration with Azure SQL DW, via Direct Query, since you can't control the queries, and in my experience you need control over the queries since you will need to tune them and things like table distribution, statistics and indexes, but you can't do that easily with dinamically generated queries. Different queries based on the same data model might need different tuning. On the other side, loading the data into Power BI model is usually not an option in this scenario. – Marco Ramírez Dec 29 '16 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.