June 22, 2009 at 8:38 am
I am not clear what Query hints and table hints do? When are they used?
June 22, 2009 at 8:49 am
If you have do ask then you dont need to know 🙂
Basically they allow you to override the optimizers functionality to specify an execution path.
You only need to use them , when the optimizer has got it REALLY wrong , and review all queries using hint after a service pack install. You never know what MS may of fixed.
June 22, 2009 at 9:48 am
sarvesh singh (6/22/2009)
I am not clear what Query hints and table hints do? When are they used?
Have you looked in SQL's Books Online? There's a relatively good description of all the hints and what they do. If there are any that you don't understand, post here and we'll try and explain.
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
June 22, 2009 at 11:01 am
for e.g in the below query what does the merge join do? How does it optimise the query/join? What if there are three or four different joins; what will merge do?
USE AdventureWorks;
GO
SELECT *
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
Go
Is it recommended to use the Query hints or Table Hints. The query can be optimised in other ways as well.
I read the BOL it's still not v clear to me...Is there an article anywhere which i can read.
Many thanks.
June 22, 2009 at 2:03 pm
sarvesh singh (6/22/2009)
for e.g in the below query what does the merge join do?
Force SQL to use a MERGE join to join the tables, regardless of whether a merge, loop or hash join is most optimal.
http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/
What if there are three or four different joins; what will merge do?
They'll all be joined with a merge join, if possible. That's what the hint says. If it's not possible to use a merge join, you'll get an error.
Is it recommended to use the Query hints or Table Hints.
Only if you're 100% sure that you know better than the query optimiser how the query should be run. If you're having to ask what a hint does, then you shouldn't use it.
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
June 22, 2009 at 2:20 pm
Thanks Gail
I'll read up more on this and do some tests on our test server before using it in live systems.
June 22, 2009 at 2:58 pm
sarvesh singh (6/22/2009)
I'll read up more on this and do some tests on our test server before using it in live systems.
Don't use hints unless you are absolutely, totally, 100% sure that there's no other way of getting the query to run optimally. The chance that you know the query and the data better that the query optimiser is small. Usually there are better ways to optimise queries that remove the need for hints. In 6 years of doing performance tuning, I've used join/index hints twice.
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
June 22, 2009 at 3:20 pm
Thank you Gail i'll keep that in mind. Thank you for ur valuable suggestion
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply