I'm considering dropping an index from a table in a SQL Server 2005 instance. Is there a way that I can see which stored procedures might have statements that are dependent on that index?
A:
Nope. For one thing, index selection is dynamic - the indexes aren't selected until the query executes.
Barring "HINT", but let's not go there.
le dorfier
2009-01-12 22:25:22
you can still check if the index is being used by querying the sys.dm_db_index_usage_stats DMV, see my answer below
SQLMenace
2009-01-14 17:27:32
A:
As le dorfier says, this depends on the execution plan SQL determines at runtime. I'd suggest setting up perfmon to track table scans, or keep sql profiler running after you drop the index filtering for the colum names you're indexing. Look for long running queries.
JD Conley
2009-01-12 22:37:03
you can still check if the index is being used by querying the sys.dm_db_index_usage_stats DMV, see my answer below
SQLMenace
2009-01-14 17:31:00
+1
A:
First check if the indexes are being used at all, you can use the sys.dm_db_index_usage_stats DMV for that, check the user_scans and the user_seeks column
read this Use the sys.dm db index usage stats dmv to check if indexes are being used
SQLMenace
2009-01-14 17:26:43