February 3, 2005 at 2:14 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/optimizerjoinmethods.asp
February 9, 2005 at 5:07 am
I really like these types of articles because it is interesting information that I wouldn't normally go looking for, though I probably should. I might be able to get this information from BOL but has anyone ever sat down and just read BOL? Not very fun.
Is there any chance of a sequel that demonstrates how one might modify a query to get different results? I know most of it relates to indexing but if there is any way to give the SQL Server engine some subtle clues as to how to perform the join rather than using query hints, that would be pretty cool.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
February 9, 2005 at 10:05 am
Bryant,
You are right about the indexes being one of the keys. Another is size of the data inputs. It is hard (nearly impossible) to "push" the optimizer to choose one join over the other without adding/removing indexes or dramatically growing/decreasing the size of the result set.
I usually scan for the join type and if it is not the one I think the optimizer should be using (my best guess) then I try to figure out why. Most of the time hash joins show up when I think merge joins should have been used. I can usually trace these back to lack of indexes or out-dated statistics. Every now and then I find a nested loop join when I think the optimizer should be doing a merge. (My guess is usually based on result set size.) I sometimes try to push the optimizer into a merge, but in the end this does not really buy me much in optimization.
Randy
February 10, 2006 at 11:26 am
Randy,
This is a great article on a difficult subject, and you have explained it well. IMHO however, it seems that a discussion on table/column/index statistics and the influence they play on the optimizer (or even a mention) was missing.... I come from the Oracle world and the optimizer is a favorite subject (see http://www.ioug.org/select/articles/05q2/index.cfm for an article I wrote).
Regards,
John Kanagaraj
May 19, 2010 at 1:42 pm
I have a question... Is it possible to tell SQL Server to use a particular join method? Or do you always rely on the Query Optimizer to choose the most appropriate method?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply