November 4, 2007 at 3:15 pm
Hi All,
Is it a good idea to use table hints in query? I just joined this company that has many query using table hints in one of the table. The table itself is around 9mil records with a lot of indexes. What is the benefit of table hints?
I posted the query as an example
select *
from tblRequest r with(index(IX_tblRequest_Checkout))
left outer join tblMember m on r.RequestID = m.RequestID
where r.CheckOut >= dateadd(d, -30, @MemPrevDate)
and r.CheckOut < dateadd(d, -30, @CurDate)
and r.InsertDate > @MOBStartDate
and r.Status = 'FKK'
option(force order, loop join)
Also what is the reason of using option (force order, loop join)? What is the different from wwith(index(IX_tblRequest_Checkout)) ?
Thanks again.
Regards
November 5, 2007 at 2:12 am
BOL says -
Table Hints
A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.
Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that only be used as a last resort by experienced developers and database administrators.
I say - Hints should be used only when you are sure that the hint used by the query optimizer will produce a better plan than the default plan (plan without the hint). Also, you have to keep in mind about the changing statistics and data distribution over a period of time to know whether the hints are still effective.
-Pranjal
November 5, 2007 at 7:00 am
I normally recomend that people stay away from query hints of any form unless they really, really know what they are doing and are sure that the hints help.
Even then, check every couple months and make sure they still help.
Problem with a query hint is that it isn't a hint, it's an order. The optimiser must obey the hint, even if the plan it produces with the hint is far less optimal than one it could come up with if the hint wasn't there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2007 at 7:21 am
The biggest advantage of using hints is that you KNOW how it is going to execute. If consistency is more important than performance this may be the right idea.
I once had an application where the screen took normally 1 sec to load, but about once or twice a day it would take 10 seconds. I added a hint and it took the 1 second every time. The issue was being caused by a scenario where the statistics were not valid. This is also a reason for "auto update statistics", however that is another issue. However, if your settings do have "Auto Update Statistics" false, then that may be why the hints are there.
November 5, 2007 at 7:52 am
any hint is a means which you can use to actualy influence a queries behaviour !
It should be an exception to use a hint, and its purpose should be tested with every implementation of a sqlserver hotfix, sp , release.
This is a double-edged knife ! :ermm:
It may perform very well when you implement it, but statistics, spaceusage, cardinality, frag. ratio,... may change over time, and may cause your hint to be less accurate.
So you should check if your hint is still accurate over time and maintain it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2007 at 11:13 am
Don't misunderstand me. I don't EVER encourage hints. There is almost always a better way, however there are some cases where the optimizer for some reason or another can't get the right plan without help. This is about the only time you use a hint.
November 5, 2007 at 2:01 pm
Thanks for reply everyone. The posts are really help.
November 5, 2007 at 2:13 pm
I personnally do not suggestion using query hints (especially within stored objects like SPs and Views) unless you document why you do it and don't forget to let everyone know where the documentation is. Big problem with index hint is if you change design and the hint remains. Might cause unexpected delays, and you never know when that particular index might become the poorer choice naturally anyway.
But in the off chance I am trying to data for testing or reporting myself I may use a hint. Also while trying to optimize a query I may use a hint to control the behavior to give me some ideas of what index may give the best performance then work to make sure the query itself will take maximum advantage of that index without the hint.
November 6, 2007 at 6:19 am
I have seen situations where query hints were vital. see http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2005-123 it relates to indexing heavenly bodies and uses a TVF as a kind of spatial filter. Without query hints the spatial index was often the last thing to be evaluated even though it has the biggest impact especially on speed..
November 6, 2007 at 6:38 am
John Diss (11/6/2007)
I have seen situations where query hints were vital. see ..
indeed, but that should not be a general practise
and these cases' hint accuracy should be re-evaluated often.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2007 at 6:48 am
Catcha,
Someone tried to force some serious tuning on that query... the FORCE ORDER tells the optimizer to only consider the tables in the same order as they appear in the joins and the LOOP JOIN tells the optimizer to only use loop joins (as opposed to Hash Joins and other types of joins that appear in the Execution Plan). Last but not least, the table hint of WITH (INDEX()) tell's the optimizer to not even look at other indexes and to use the given index...
... basically, most everything the optimizer could do has been overridden, in this case.
Right from the git, I'd start off by saying look at the ACTUAL execution plan for a run (Estimated execution plan is sometimes misleading). If it looks good (whole 'nother story there) and the query runs in a realtively short (or at least acceptable) time, you may want to leave things alone. Then again, I'm always curious about such things and the first thing I'd likely do is remove the hints one at a time and check the execution plan just to see what happens. 😛
Now... the real question is why did the author think that such control over the optimizer was necessary? The answer is SELECT * which normally won't allow anything better than an INDEX SCAN and frequently causes table scans. I think I'd first find out if returning every column in the table was actually necessary because the first step towards tuning is to return only the necessary data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply