Determine if an Index has been used as a hint

Question

In SQL Server, there is the option to use query hints. eg

SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))

I am in the process of getting rid of unused indexes and was wondering how I could go about determining if an index was used as a query hint. Does anyone have suggestions on how I could do this?

Cheers, Joe

Answer 1

That's a great question, and I don't think I can give you an easy answer. If it were me, I would script th entire database in Management Studio and do a Text search for the index name. I would also do that in all of my reports and source code, just to be sure, too.

I don't think that hints make their way to sys.dependencies for procs an functions, but even if they did, you'd have some ad-hoc SQL to potentially deal with, so that's why I'd use the text searching route.

Answer 2

You can only run profiler for client SQL or search sys.sql_modules otherwise.

To find unused indexes you'd normally use something based on dmvs. This would show you what indexes are in use and need to be kept.