A short article about capturing transient telemetry.
Most DMVs in the Azure SQL data warehouse are transient, which means data stored in the table is not persisted usually after:
- A Pause / Resume / Scale event
- After a max rollover threshold has been reached (10000 rows)
For example, the data in the sys.dm_pdw_request_steps dmv – a very important dmv which provides a simple query plan for executing queries, will be lost if the events above are encountered.
There are many patterns to persist such data but a simple pattern is shown below, where the telemetry data is copied to a persisted table in the data warehouse and then can be used in reporting or for troubleshooting at a later time in an Azure SQL database.
In my solution below I have created a function app which triggers the copy from dmv to permanent table frequently. An external table is created in the Azure SQL db to the Azure SQL DW to view the data. One can also copy the data from the external table into a local table in the Azure SQL db for performance improvements say in reports.
Next post I’ll provide the code.
peter.irojah.innovation 