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:

IndexRobot

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

detailed

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

chrome_error

Azure’s Recommendation:

CREATE NONCLUSTERED INDEX [nci_Robot] ON [dbo].[p_Def] ([Revis], [Guid]) INCLUDE ([visionI) WITH (ONLINE = ON)

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:

inthecloud

SELECT
  OBJECT_NAME(id.[object_id])
  AS [TableName],
  ID.equality_columns,
  ID.included_columns,
  IGS.user_seeks,
  IGS.user_scans,
  IGS.last_user_scan,
  IGS.last_user_seek
FROM sys.dm_db_missing_index_details AS ID
INNER JOIN sys.dm_db_missing_index_groups AS IG
ON ID.index_handle = IG.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS IGS
ON IG.index_group_handle = IGS.group_handle
WHERE   OBJECT_NAME(id.[object_id]) = 'p_Def'
ORDER BY   IGS.user_seeks DESC

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.

cols

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.

Plan ID 6834 is looking good now.

querystore

 

 

Advertisements

2 thoughts on “Azure Robots – Index Performance Recommendation

    • 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 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.