Confusing performance advice from SQL Server Management Studio

Question

I am working on improving our SQL-performance. And SQL Server Management Studio gave an advice which was surprising to me. For the example's sake, I'm going to simplify the table structure and the advice that SSMS gave.

Table structure of Person:

• id
• first_name
• last_name
• date_of_birth
• email

I had a particular query for which I showed the execution plan. Then I saw the advice that SSMS gave and it surprised me a little bit. It recommended to add two indexes with the following signature which should improve performance by 41% and 53% respectively:

CREATE NONCLUSTERED INDEX [person_1]
  ON [Person]([first_name],[last_name])
  INCLUDE ([id],[date_of_birth],[email])

CREATE NONCLUSTERED INDEX [person_2]
  ON [Person]([first_name],[last_name])
  INCLUDE ([id],[email])

These two indexes are on the same columns. Only the INCLUDE columns are different. My understanding from reading about indexes is that the first index includes the data for the second index as well. So why does SSMS recommend the second index as well as the first one already includes the needed data?

Answer 1

Never use SSMS suggestion as granted. The suggestion is based on a particular query.

Rather explore system views like sys.dm_db_missing_index_details. See more on sys.dm_db_missing_index_details

or check out Glenn Berry's DMV Queries on SQL Server Diagnostic Information Queries for April 2019

… or even better take Brent Ozar sp_blitzindex BrentOzarULTD/SQL-Server-First-Responder-Kit

… or take this query

SELECT DB_NAME(database_id) AS database_name, 
       OBJECT_NAME(object_id, database_id) AS table_name, 
       mid.equality_columns, 
       mid.inequality_columns, 
       mid.included_columns, 
       (migs.user_seeks + migs.user_scans) * migs.avg_user_impact AS Impact, 
       migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS Score, 
       migs.user_seeks, 
       migs.user_scans
FROM sys.dm_db_missing_index_details mid
     INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
     INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) DESC;