Duplicate index - Same key, one with INCLUDE columns, which one to drop?

Question

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

• Marian: I think you answered yourself here. Keep the index that's more useful (more reads) :). Test if the query plans that use the Include index change to OK plans, not extremely bad ones, as they will probably switch to Key Lookups. If the new plans are acceptable in performance then I see no reason to keep the Include index. Use sp_blitzIndex from Brent's gang to see more info regarding your indexes.

Answer 1

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.

• Jerry Hung: Sorry it was my typo. [ID] is the secret column for both and I've removed it. both have same key on [EXECUTION_TIME] only. 1 has no include, 1 has 6 included columns

Answer 2

After reviewing the query plans using the IndexInclude, I've confirmed they don't use the 6 Included columns really (mostly SELECTs), so I've disabled the IndexInclude.

So far so good while I continue to monitor

These are the queries I used to find them (source from SQLSkills and Kendra) https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/ https://littlekendra.com/2017/01/24/how-to-find-queries-using-an-index-and-queries-using-index-hints/

I combined the two and modified to get what I need (see actual text/query, execution count, reads/writes, last execution time)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DECLARE @IndexName AS NVARCHAR(128) = 'IndexInclude';
-- Make sure the name passed is appropriately quoted 
IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName); 
--Handle the case where the left or right was quoted manually but not the opposite side 
IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName; 
IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']';
-- Dig into the plan cache and find all plans using this index 
;WITH XMLNAMESPACES 
 (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text, 
--obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName, obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName, obj.value('(@Table)[1]', 'varchar(128)') AS TableName, 
--obj.value('(@Index)[1]', 'varchar(128)') AS IndexName, obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind, 
--cp.plan_handle, 
qp.query_plan,
cp.usecounts AS execution_count
,
 querystats.execution_count,
 querystats.total_logical_reads,
 querystats.total_logical_writes,
 querystats.creation_time,
 querystats.last_execution_time
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj) 
--added by Jerry to get the time
INNER JOIN sys.dm_exec_query_stats querystats WITH (NOLOCK) ON querystats.plan_handle = cp.plan_handle
ORDER BY querystats.last_execution_time DESC
OPTION(MAXDOP 1, RECOMPILE);