We are working on 2 solutions:
- Auto-cancel long running queries on SQL DW that are executed by certain set of Users (that belongs to a workload group).
- Auto-scale Service tier for SQL DW.
These 2 solutions would work based on the current OR aggregated (over 30 mins) consumption of Data warehouse units and ratio of Active/Queued queries on SQL DW.
We want to create Data Factory Pipeline that'd trigger on tumbling window of 30 minutes. and we are trying to figure out a way we can do this with a single Stored Procedure call from ADF Pipeline.
The SP should check the DW consumption, if possible using DMVs.
Is it possible to get following things from DMVs:
- Current DWU Consumption of SQL DW
- Current Active vs Queued queries: i think we can use a join of exec_request and exec_session
If not possible through DMV, is it possible to get this data from Azure Monitor on Data Factory Pipeline
