How did you determine that the issue with the indexes? Did you use any SQL performance monitoring tools that point you to the indexes or it's just a guess?
Hits are usually the last resort when you can't rewrite the query to have the right execution plan, or when you query is quite complex and can't pickup the right indexes, so you point your query to the right stuff. Michael Nemtsev Microsoft MVP B: http://msmvps.com/blogs/laflour S: http://www.sharepoint-sandbox.com From: [email protected] [mailto:[email protected]] On Behalf Of Maddin, Peter Sent: Monday, 21 June 2010 7:57 PM To: [email protected] Subject: Embedding SQL index hints into SQL commands for SQL SERVER. I have a DBA that is suggesting that to improve performance I need to embed optimizer hints in the SQL commands that I use against the database so it will use specific indexes. Is this a good idea? I believe performance problems are related to the size of the database as my test database (2 GB) has no problems at all and the production database has grown large and fat with lots of unnecessary records that should have been purged years ago. I would have thought that embedding optimizer hints into a SQL statement in my code increases the level of coupling between my code and the database which is not a good thing. Also it's the role of the optimizer to determine what indexes (provided they exist) to use. Has anyone else done this sort of thing? Am I just being stupid (or more so than usual)? Regards Peter Maddin Applications Development Officer PathWest Laboratory Medicine WA Phone : +618 9473 3944 Fax : +618 9473 3982 E-Mail : [email protected] The contents of this e-mail transmission outside of the WAGHS network are intended solely for the named recipient's), may be confidential, and may be privileged or otherwise protected from disclosure in the public interest. The use, reproduction, disclosure or distribution of the contents of this e-mail transmission by any person other than the named recipient(s) is prohibited. If you are not a named recipient please notify the sender immediately.
