Query Hints - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Hints

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-09 : 08:11:25

Hi Team,

Could you please tell some scenarios where query hints will give enormous performance benefits? (with sample queries)

One example I found is listed below [ This explains the hint “with (index=” ]
http://blogs.msdn.com/queryoptteam/archive/2006/04/12/575241.aspx

Thanks
Lijo Cheeran Joseph

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-09 : 10:10:52

Very, very, very, very few. Only time you want to use hints is when you are absolutely certain that you know better than the optimiser for any and all possible calls to this query what the best way to run it is. You then need to test any and all possible calls to the query and ensure that your hints really do make the query faster. You need to continue testing at regular intervals to ensure that data changes (or patches) haven't made your hinted query less efficient than what the optimiser would have come up with itself.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources