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:

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:

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.

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.

Filed under: Azure, Azure SQL DB Tagged: Admin, Azure, Indexes, SQL database