Retrieving the SQL Query Plan using the Azure SQL query ID

I was working with a vendor in pinpointing performance issues that we were having on an Azure SQL database. We noticed some performance problems with this database in the "Query Performance Insight" tab under "Intelligent performance" in the Azure Portal.

This tab will show you the most resource consuming queries by Query ID together with an example of the query itself. However, to really figure out why this query is slow you probably want to pull the query plan. Luckily, we can do this quite easily with the following query using the aforementioned Query ID:

SELECT TOP 1 p.plan_id, p.query_plan 
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_plan qs ON qs.plan_id = rs.plan_id
WHERE qs.query_id = CHANGE_QUERY_ID_HERE

Then save the query plan to a .sqlplan file and open this is SQL Management Studio.

If your SQL plan gets truncated because its too big you can change the maximum characters retrieved under:

Tools Options → Query Results → SQL Server → Results to Grid