CODEWITHSUNDEEP
sql-serverazure-synapse
ramees
ramees

Reputation: 61

How to monitor older query history and query plan in Azure Synapse Dedicated pool

I am trying to query the dmv's sys.dm_pdw_exec_requests and sys.dm_pdw_sql_requests. But they don't seem to be showing historic queries. I want see the queries which ran yesterday, analyze there query plan, resource class and memory used etc.

Upvotes: 2

Views: 2685

Answers (1)

RobertMcClean
RobertMcClean

Reputation: 21

Not sure if you still need an answer to this but sys.dm_pdw_exec_requests only keeps information for the most recent 10,000 queries. Depending on the workloads your running that could mean it only is holding a few hours worth and pause and unpause will blow that out as well.

For long term tracking we were able to keep some of that information using this synapse tool box - https://github.com/microsoft/Azure_Synapse_Toolbox

It won't help with everything you want to look back on such as the query plans but it is useful. Once you get the dashboard setup along with log analytics you can at least get some insight into what happened the prior day(s). Using this we found concurrency to be one of our major issues that we needed to resolve.

Upvotes: 2

Related Questions