Find the queries that are using each index to do a search or scan.

THEN

Look at the code and see if the entire index is being used.

Do this for all queries until you have an exact idea what will happen if you drop one or the other index, or even, both.

You could calculate what index is actually needed and that might be neither of the 2.

THEN

Drop both indexes in QA and see what the system asks for after a week or so and see if the missing index is confirmed by your calculation.

THEN

Apply the change to DEV and QA.

How do I find the queries and procs which may be hitting this index?

DECLARE @IndexName AS NVARCHAR(128) = 'IX_my_Index_That_I_Am_thinking_of_dropping';

-- 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,

object_name(qp.objectid),

query_plan

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)

OPTION(MAXDOP 1, RECOMPILE);