I know this thread has been quiet for a while, but I came across it while trying to do a similar thing myself. If sys.dm_db_index_usage_stats tells you that an index you want to drop is being used, how do you find what is using it?

Vinay, your logic is sound, it's just that your XPath query wasn't quite right. I settled on the following query to get me info on what plans use my index. It differentiates between Seeks, Scans, and Updates (the latter of which shouldn't prevent you from dropping your index).

Of course, as GilaMonster indicates, this only tells you about plans in the cache. There could be a rarely-used proc not in the cache still lurking out there that wouldn't show-up on this query.

Here is the SQL... just replace 'MyDatabase' and 'MyIndex' with your own values. This works on SQL 2008 (v10.0.5768.0). I haven't tested it on other versions.

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

DB_NAME(E.dbid) AS [DBName],

object_name(E.objectid, dbid) AS [ObjectName],

P.cacheobjtype AS [CacheObjType],

P.objtype AS [ObjType],

E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=''[MyIndex]''])') AS [ScanCount],

E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=''[MyIndex]''])') AS [SeekCount],

E.query_plan.query('count(//Update/Object[@Index=''[MyIndex]''])') AS [UpdateCount],

P.refcounts AS [RefCounts],

P.usecounts AS [UseCounts],

E.query_plan AS [QueryPlan]

FROM sys.dm_exec_cached_plans P

CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E

WHERE

E.dbid = DB_ID('MyDatabase') AND

E.query_plan.exist('//*[@Index=''[MyIndex]'']') = 1