SQL Server – How to Handle Duplicate Index with INCLUDE Columns

duplicationindexsql serversql server 2014

I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop?

They are the same, except IndexInclude has 6 INCLUDE columns.

Both have some reads (IndexNoInclude has much higher seeks).

I believe the IndexInclude will satisfy anything that would've used IndexNoInclude right? Since the keys are exactly the same.

Thanks for all the help in advance.

Output from sp_Blitz. Sorry for the bad format 

Details: db_schema.table.index(indexid)    
Definition: [Property] ColumnName {datatype maxbytes}   Secret Columns  Fillfactor  Usage Stats Op Stats    Size

IndexINCLUDE [1 KEY] EXECUTION_TIME {datetime 8} [6 INCLUDES]  
Reads: 101,913 (101,913 seek) Writes:2,381,661,369  
0 singleton lookups; 225 scans/seeks; 0 deletes; 0 updates;     263,358,683 rows; 5.4GB

IndexNoInclude   [1 KEY] EXECUTION_TIME {datetime 8} 
Reads: 1,181,595 (1,181,595 seek) Writes:2,380,483,678  
0 singleton lookups; 15 scans/seeks; 0 deletes; 0 updates;  263,358,683 rows; 2.9GB

Best Answer

I believe the IndexInclude will satisfy anything that would've used IndexNoInclude right? since the Keys are exactly the same

Are they the same? Maybe I am misreading the output, but it certainly looks like IndexNoInclude has an extra key column: [ID] NUMERIC.

You should review your queries to see which of the 6 INCLUDE columns are actually being referenced / used. You could likely get away with "merging" the two indexes such that the remaining index has both key columns and between 1 and 6 of the INCLUDE columns.

Related Question