Sql Server keeps on changing warning to build Non clustered Index on a table.

Sql Server keeps on changing option to build Non clustered Index on a table. 1st time, SQL Server gave warning to build a index on 18 columns to improve performance by 55%. I build those indexes and then the SQL ran for 16 minutes and gave new warning to build index differently on the same table to improve performance by 70%. However, when I run the SQL after implementing new Indexes, it runs for over 30 minutes. Is there any recommendation on what attributes should be included for Non clustered right after table name and Include option?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Feb 28, 2020 at 07:43 PM

SQL Server will suggest indexes based on current query patterns. It's not uncommon to see similar missing indexes within the DMV. This is and should be a moving target that has to be re-addressed periodically. Usually a good rule of thumb is that the key columns should be the predicates and join criteria and any includes the columns returned (as a starting point).

0 ·