Depth of sys.dm_pdw_exec_requests on Azure SQL Data Warehouse

Question

I am running tests that take many hours to complete on ADW and the amount of SQL involved rolls off the 10,000 row limit of sys.dm_pdw_exec_requests (as documented at https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-service-capacity-limits ) in less than 30 minutes.

Is my only option to create a process to capture into a table in my database the data on sys.dm_pdw_exec_requests every N minutes (where N << 30 )?

Answer 1

I'm not sure what your use case is, but perhaps you can get the same useful information out of the audit logs?

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

Answer 2

You might be able to use something that was already built for that purpose, instead of reinventing the wheel:

https://github.com/andrealibero/Azure_SQL_DWH_Perf_Stats

the PowerShell script can collect output of DMVs (configured in an XML file) in a loop or for a number of specified iterations.

Given how quickly the DMVs roll out for you this might help in your scenario.