Post navigation

Azure Robots – Index Performance Recommendation

They are watching me and my Azure SQL Database and recently I noticed a low impact performance recommendation was made. Naturally I became very interested. Within your database (when in the portal) under operations you may notice something similar to the below:

It is basically a recommendation to create a non-clustered index.

The latest version of chrome does not support copying? Anyways, once copied the recommendation was as follows:

Now, I have no idea what they are basing this on? Maybe this is a hint you get if running the code where SQL Server returns you a missing index hint? Or maybe it uses the missing index DMVs? I decided to find out, so I connected to Azure SQL Database and within the context of my database I ran the following code block:

I ordered by the most user seeks and last seek time which was within the last few minutes. Why user seeks? This is the number of seeks caused by user queries that the recommended index in the group could have been used for. Also, worth noting, even though I don’t show it but the query also had a high avg_user_impact.

Well, the structure matches what Azure was recommending (I had to obfuscate it). This is not a post on indexing and what is right or wrong, more so, where Azure comes up with its recommendations.

Ok, I could not resist and I ran it (naturally I found the SELECT statement) and checked out Query Store post change.

They went after the one with highest avg impact ( I had others but they werent selected)…I have another post where I mention a conversation with a PM @ MS and from that I feel something else is also behind it like time slice analysis… all good fun right 🙂