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

 

 

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 🙂

      Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s