InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:43:58 GMT20http://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxI 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 [i]is[/i] being used, how do you find [i]what[/i] 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.[code="sql"]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 PCROSS APPLY sys.dm_exec_query_plan(P.plan_handle) EWHERE E.dbid = DB_ID('MyDatabase') AND E.query_plan.exist('//*[@Index=''[MyIndex]'']') = 1[/code]Thu, 18 Oct 2012 07:47:28 GMTstephen.kratowiczhttp://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxThe only way you can do that is to query the plan cache, read through the xml of the query plan and see if the index is there. If there are no plans that have reference to that index, it just means that there's no such plans in cache, nothing more.I know there have been blog posts written on this, hit google and see what turns up.Tue, 04 Jan 2011 10:53:25 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxThank you for the reply, Ok let me kind of get this right,Used the index usage dmv to figure out what indexes are not used very often.found few indexes which run very infrequently used but they are seeks, The amount of seeks will not help becuse the query may run once a week or a end of day process.so i wanted to know if drop an index which i have identified by the query stats. then what queries will be affected when i drop this perticular index.just to make sure the infrequent queries dont impact the SLA's which are setup for the EOD or EOM processes.To Re-iteratethe steps i followed 1) use Sys.DM_DB_INDEX_USAGE_STATS[code="sql"]SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID, S.*FROM SYS.INDEXES I INNER JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S ON S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @dbidWHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.NAME IS NOT NULL and OBJECT_NAME(I.OBJECT_ID) not like 'sys%' AND I.NAME not like 'PK%' -- Ignore Pk's and I.NAME not like 'UC%' -- Ignore unique constraints.ORDER BY (s.user_seeks+s.user_scans+s.user_lookups+s.user_updates) asc[/code]2) Use the Indexes which are not frequently used. Do an impact analysys.[code="sql"] select object_name(objectid), query_plan.query('count[//@Index=''[IDX_SettledMonthToDate]'']') as result, query_plan from sys.dm_exec_cached_plans pl cross apply sys.dm_exec_query_plan(pl.plan_handle) where dbid = 14 [/code]This code is not returning all the query plans used by sql server. and unable to see what processes/procedures/AdhocSql are using this perticular indexCould you please help me with a query so that i can isolate the sql text which is going to be using this perticular index or may be the procedure name which will be impacted.This is what i want to know. plese help.Tue, 04 Jan 2011 10:22:06 GMTbhushanvinayhttp://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxVinayWhat do you mean by the query stats? I would recommend that you monitor dm_db_index_usage_stats over a period of time. That way you'll find out how often the index is used, and get an idea of whether the regularity of its use justifies the expense of maintaining it.JohnTue, 04 Jan 2011 08:23:06 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxThank you John :-DThat was indeed not what i expected, the idea is that its an index which i think is not used in the query stats only some amount of seeks are there, but wnated to make sure the amount of seeks it does will not impact the major queries and there are 100's of procs in the db.i dont want some odd process to start slowing down and not hit my SLA's.Just wanted to know if the index in question is beeing used in any query plans.Kind RegardsVinay[quote][b]John Mitchell-245523 (1/4/2011)[/b][hr]VinayIf your index lives up to its name and is indeed unused, then no queries will be affected if you drop it. Therefore it is sufficient to use the dm_db_index_usage_stats DMV over a period of time to verify that the index is never used, before you drop it.John[/quote]Tue, 04 Jan 2011 08:15:52 GMTbhushanvinayhttp://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxVinayIf your index lives up to its name and is indeed unused, then no queries will be affected if you drop it. Therefore it is sufficient to use the dm_db_index_usage_stats DMV over a period of time to verify that the index is never used, before you drop it.JohnTue, 04 Jan 2011 08:11:01 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1042434-149-1.aspxIf i want to delete my IX_Unused_Index but before that i want to identify which queries will be affected if i do drop this index.how do i go about this.Tried the follwoing, but no luck, i belive i am not the first person to go through this issue, i hope somebody has already got a script which does this.select object_name(objectid), query_plan.query('count[//@Index=''[IX_Unused_Index]'']') as result, query_plan from sys.dm_exec_cached_plans pl cross apply sys.dm_exec_query_plan(pl.plan_handle) where dbid = 14 but looking at the select *from sys.syscacheobjects cowhere dbid = 14gives me all prepared and compiled plans but does not give the plan itself.may be i am getting confused here with syscacheobjects and dm_exec_query_plan function.please help.Kind RegardsVinayTue, 04 Jan 2011 07:58:56 GMTbhushanvinay