Dedicated SQL pool - How to retrieve historic query execution data after restart

Question

Hi, I'm testing synapse dedicated SQL pool database. As part of the test I'm scaling the database up and down and I pause it at off hours. Each time I scale or restart the database all previous data from dm_pdw_exec_requests and sys.dm_pdw_request_steps is cleared. Is there a way to get historic query execution data prior to last restart/scale?

Answer 1

Hello Shmuel Kaplan, Welcome to the MS Q&A platform. Yes, you are correct. when you scale or restart a dedicated SQL pool, the data in sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps DMV views are cleared.
To capture historic query execution data before the last restart or scale operation, you need to implement a custom solution to capture and store this data. One approach is to use Azure Log Analytics to capture and store the query execution data. You can configure Azure Synapse Analytics to send logs to Azure Log Analytics, and then use Log Analytics to query and analyze the data. <below steps copied from documentation page> Configure diagnostic settings Create or add diagnostic settings for your workspace, dedicated SQL pool.

1. In the portal, go to Monitor. Select Settings > Diagnostic settings.
2. Select the Synapse workspace, dedicated SQL pool for which you want to create a diagnostic setting.
3. If no diagnostic settings exist on the selected workspace, you're prompted to create a setting. Select Turn on diagnostics. If there are existing diagnostic settings on the workspace, you will see a list of settings already configured on the resource. Select Add diagnostic setting.
4. Give your setting a name, select Send to Log Analytics, and then select a workspace from Log Analytics workspace. Note Because an Azure log table can't have more than 500 columns, we highly recommended you select Resource-Specific mode. For more information, see AzureDiagnostics Logs reference.
5. Select Save.

After a few moments, the new setting appears in your list of settings for your workspace, dedicated SQL pool. Diagnostic logs are streamed to that workspace as soon as new event data are generated. Up to 15 minutes might elapse between when an event is emitted and when it appears in Log Analytics. Reference document: https://learn.microsoft.com/en-us/azure/synapse-analytics/monitoring/how-to-monitor-using-azure-monitor#configure-diagnostic-settings I hope this helps. Please let me know if you have any further questions.