Tracing SQL queries for dedicated SQL pool to Synapse integration pipelines
In the Using Kusto queries to monitor SQL and Spark pools recipe of Chapter 11, Monitoring Synapse SQL and Spark Pools, we explored using Kusto queries and a Log Analytic workspace to find expensive queries in a dedicated SQL pool. However, in data engineering projects, finding the expensive queries in a dedicated SQL pool alone wouldn’t be sufficient as you need to find the details about the integration pipeline that fired the query. To do this, we need to find a way to correlate the Log Analytics data from the integration pipelines and a dedicated SQL pool.
Fortunately, Copy activity in an integration pipeline automatically adds a label to the SQL query it uses to copy the data. We can easily identify the pipeline and activity name from the label attached to the SQL query in the dedicated SQL pool. However, other activities, such as data flows and SQL stored procedure tasks, don’t automatically...