I have a number of queries that I would like to optimise to be more efficient for an Azure Synapse Analytics instance.

So far I have been comparing estimated execution plans of the existing query to an updated query as a means to compare efficiency. I get the execution plans using the explain keyword. I have been aiming to reduce the <dsql_operations total_cost="xxx" figure, as well as the total number of operations.

The first query I optimised I reduced the total_cost from 37000 to 300, and the number of operations from 97 to 5. This was achieved by arranging the data into a star schema so that almost all the work can be done via a pass through query.

To my surprise, both queries complete after a similar duration (around 1:30) and both yield the 3m rows as expected.

I suspect the second query is more efficient in terms of resource usage, even if it isn’t much faster.

Looking in Azure’s portal at the Query Activity tab I can only see a basic overview of the query (Duration and Resource allocation) which isn’t very helpful. The Metrics tab has a little more information, but it is aggregated so can’t be used either.

How can I get more detailed query performance information? (not just query duration)

Thanks