SelecTViews
With Stephen Wynkoop
Find out the latest news involving the Office 365 launch, LaCie’s CloudBox and more.
Query Plans –Who is Using Them?
In a response to my interest in how folks are using query plans, Neil writes with some tips he has found.
One of the key things Neil points on is how to handle Nested Loop Joins against large tables. If you are not familiar with a Nested Loop Join, that is when SQL Server takes the results of a previous set and then looks up the appropriate record one at a time in the table being joined. Often if there is an appropriate index on the table being joined a Nested Loop Join is the best option. However, if the set that is being scanned is much smaller than the table being joined, a Nested Loop Join is often not the best method.
This is one of the scenarios a Query Plan will help you recognize. Neil does a good job of describing options for resolving this situation.
Neil:
The best in-depth information I have found so far on Query Plans is Grant Fritchey’s “Dissecting SQL Server Execution Plans”, if you have not done so already I recommend you check it out, it is a free e-book that can be easily found by Googling. Performance tuning is more art than science and understanding and correctly interpreting the information presented in Query Plans is a key part of the art. I have found that the Estimated Plan can differ from the Actual Plan and so it pays to look at the Actual Plan to get the best look at what is going on in the plan. Also I have found that the XML representation of the query plan holds subtle details that are not exposed in the Graphical Representation of the Query Plan. In SQL Profiler a right-click on the “ShowPlan XML” row provides a menu option “Extract Event Data…..” which will save the XML to file, I then use Microsoft’s XML Notepad 2007 to browse and search the XML representation of the query plan.
The most common query plan challenge I have faced is when the Optimizer decides a Nested Loop join is the appropriate choice involving joins of millions of rows of data. An update of statistics or a force order on the query is usually effective to influence the Optimizer to choose an alternate plan that is more effective.
Send your tips and ideas to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Web Content Management
Written by AvePoint
The power of the World Wide Web has dramatically change… (read more)