2

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

gbn
  • 422,506
  • 82
  • 585
  • 676
Joe Bloggs
  • 105
  • 1
  • 6
  • Are you asking whether the index was ever used in a hint (including ad-hoc SQL Statements) or just if it is used in a view/proc? – JohnFx May 26 '10 at 22:06
  • I want ot know if the index was used in a view/proc. My concern is that if a remove an index that is used as a hint in a view/proc the view/proc will fail when called by the DB app, which ultimately means the end user will get some unexpected error. – Joe Bloggs May 26 '10 at 22:38

2 Answers2

1

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
0

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.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
  • How would I go about scripting the entire DB? There is an option in SSMS to script DB but this only creates a script for the DB and not the DB objects. – Joe Bloggs May 26 '10 at 22:48
  • I have put this project on hold but for anyone who is interested here is a link for scripting SQL Server 2008 DBs. http://www.kodyaz.com/articles/sql-server-script-database-by-sql-2008-script-wizard.aspx I haven't tried it out but it looks like it would do the job. – Joe Bloggs May 27 '10 at 15:24