Monitoring Azure Synapse Analytics Workloads Utilizing DMVs
On this article we’ll have a look at Dynamic Administration Views and the way can we leverage them to observe the workloads in an azure synapse analytics workload. We are going to study this at present with a sensible use case and few examples focussing on synapse workload monitoring.
Dynamic Administration Views
Dynamic Administration View or just referred to as DMVs are nothing however inbuilt features that may allow you to question and return the server state or database particular metadata info that you should utilize to observe your workloads and the well being of your synapse workloads. You should use these particulars for tuning the efficiency, discovering out for any errors, diagnose the warnings and different points within the server occasion or a selected database.
You will want view database server state stage of privilege for operating the database scoped DMVs that we’re going to use for this session.
GRANT VIEW DATABASE STATE TO <username>
It’s essential to have a devoted SQL pool to execute the queries as you can not do that in a built-in serverless pool.
The login classes to your synapse workload are logged to sys.dm_pdw_exec_sessions. The DMV comprises the latest 10okay login particulars, and it assigns the session_id sequentially for every new login session.
SELECT * FROM sys.dm_pdw_exec_sessions the place standing <> 'Closed' and session_id <> session_id();
Monitoring Question Executions
The latest 10okay queries which might be executed are logged into this DMV sys.dm_pdw_exec_requests. Querying this DMV for a specific session_id reveals all queries which have been run for a login. The request_id column that’s current on this DMV helps uniquely establish every question and acts as a major key for this DMV. It’s assigned sequentially for every new question and is prefixed with QID which stands for question ID.
Use the next step-by-step strategy to research the question execution plan for a selected question.
-- Monitor lively queries SELECT * FROM sys.dm_pdw_exec_requests WHERE standing not in ('Accomplished','Failed','Cancelled') AND session_id <> session_id() ORDER BY submit_time DESC; -- Discover prime 10 queries longest operating queries SELECT TOP 10 * FROM sys.dm_pdw_exec_requests ORDER BY total_elapsed_time DESC;
From the above question take down the request ID of the question which you wish to examine. You possibly can merely assign a remark referred to as LABEL to your question which can be utilized to lookup within the sys.dm_pdw_exec_requests DMV.
-- Question with Label SELECT * FROM sys.tables OPTION (LABEL = 'newQuery') ; -- Discover a question together with your label 'My Question' -- Use brackets when querying the label column, as it is a key phrase SELECT * FROM sys.dm_pdw_exec_requests WHERE [label] = 'newQuery';
Use the request ID you’ve taken from the above step and question it in request_steps DVMs as beneath to seek out the distributed SQL plan. It additionally helps to get info like row_count, estimated_rows, command, operation_type and distribution_type quantity many different objects out there for querying.
-- Discover the distributed question plan steps for a particular question. Substitute the request_id with worth from Step 1. SELECT * FROM sys.dm_pdw_request_steps WHERE request_id = 'QID####' ORDER BY step_index;
Monitoring Ready Queries
Generally the question you making an attempt to execute won’t make a progress or will make little or no ready for assets, which occurs more often than not in a prod setting. The beneath question reveals the precise assets a question is ready for and allows you to resolve the following plan of action. Be aware that in case your question is actively ready on assets from different queries then it should ‘AcquireResources’ state and if it has all of the required assets, then it is going to be in ‘Granted’ state.
-- Discover queries -- Substitute request_id with worth from Step 1. SELECT waits.session_id, waits.request_id, requests.command, requests.standing, requests.start_time, waits.kind, waits.state, waits.object_type, waits.object_name FROM sys.dm_pdw_waits waits JOIN sys.dm_pdw_exec_requests requests ON waits.request_id=requests.request_id WHERE waits.request_id = 'QID####' ORDER BY waits.object_name, waits.object_type, waits.state;
These are among the DMVs which might be useful in monitoring the synapse workloads in dwell setting, this might be continued by half two in coming days which could have one other essential set of DMVs.
Ideas are referred from Microsoft official documentation.
All of the queries used on this demo are sourced from official microsoft documentation.