SQL Server Index Dependence

Question

I'm considering removing an index from a table in an instance of SQL Server 2005. Is there a way to see which stored procedures can contain instructions that depend on that index?

Answer 1

Nope. Firstly, index selection is dynamic - indexes are not selected until the query is completed.

Ban "TIP", but don't let it go.

Answer 2

First check if indexes are used at all, for this you can use DMV sys.dm_db_index_usage_stats, check the user_scans and user_seeks columns

read this Use sys.dm db dmv index usage statistics to check if indexes are used

Answer 3

As le dorfier says, it depends on the execution plan that SQL defines at runtime. I suggest setting up perfmon to track table scans or keep the sql profiler running after you reset index filtering for the colum indexes you are indexing. Look for long queries.