It is really easy getting insight into Query performance within your Azure SQL DB. I think Microsoft have done a good job with the User Interaction and with the information provided – even though I would have liked to see a metric type on I/O. Never the less lets dig in.

On you Azure DB under support and troubleshooting click on Query Performance Insight.

Here you will be presented with the TOP X queries based on CPU, Duration or Execution count. You will have the ability to change the time period of analysis, return 5, 10 or 20 queries using aggregations SUM, MAX or AVG.

So let’s look at what information is provided based on queries with high AVG duration over the last 6 hours.

Once you have highlighted the section on the bar chart that interests you (red arrow), you will be presented with the duration time for that query ID. For this it is query ID 297 that had an Avg duration of 9.27S.

So you can then click the 297 ID button to dig in deeper where you can get the Query text and get timed interval analysis.

Now, there is a “recommendation” button – here you can have the ability to even automate index maintenance, personally this isn’t something I would automate – I would like to see what they recommend and analyse it myself.

Pretty powerful (and colourful) stuff available at your finger tips.

UPDATE: 6TH OCTOBER 2016 – I have been contacted by Microsoft Query Performance Insight PM where he kindly informed me that I/O is now tracked (if you recall at the start of the article it was something that I would’ve loved to see – now I can ).

So back in the Query Performance Insight menu – you will see Data IO and Log IO.

You will now have the ability to see a line graph to see a trend (if it exists) for these new metrics.

Then as mentioned before you would correlate this back to the Query ID to understand the TSQL being executed. So for this example I would dig into Query ID 8280.

It ended up being a pretty poor SELECT statement in much need of some optimization.