SelecTViews
With Stephen Wynkoop
Find out the latest news involving Adobe’s Flash Builder 4.5. What’s going on with Oracle’s lawsuit against Google? How about some tips on cloud appropriateness? Stephen answers, "What is an outer union operation and how does it work?"
How Much Do You Need to Know About Query Plans?
Like anything to do with software, you need to know as much as you have time to absorb. If your job is to make a database perform, then you need to know everything.
For example, one of the first things you do to optimize a query is to locate when the query is performing a table scan. This is when the query looks at each record in a table by sequence of it’s clustered index, or natural data order if there is no clustered index. Sometimes this is good; sometimes this is bad.
It is helpful, IMHO, that any software developer at least understand what a table scan is. If an individual is responsible for performance, they really need to understand when a table scan is a good or bad thing.
Here is another example. In SQL Server there are three different kinds of joins used when combining records from one table to another table. There is a Loop Join, a Hash Join and a Merge Join. Each of these join technologies work differently. Each of them has a sweet spot for different scenarios.
Most of the time SQL Server chooses the best join method. I have tried using query hints to change the join type, and never really found anything that outperforms the method selected by the optimizer. This behavior is especially true if you have relevant statistics that are up to date, and good index coverage.
What I have been able to do many times is to determine that SQL Server is taking a more complicated path to the final results. Temp tables, CTEs or Inline SQL are tools that many times allow me to cause SQL Server to use a different strategy without forcing query hints or indexes. The only reason I can take this approach is because I am able to understand what is going on with these different join techniques.
Back to the main question, “How Much Do You Need to Know About Query Plans?” It would seem to me the answer depends on your job and interest.
What do you think? What is your job role, and how has understanding or not understanding query plans helped you?
Send your thoughts to btaylor@sswug.org.
Cheers
Ben
$$SWYNK$$
Featured Article(s)
Planning for the Cloud: Data Architecture in Hybrid Solutions
With the release of Office365 scheduled for June 28, 2011, the ability to develop and deploy SharePoint solutions in the cloud is now reality.
Featured White Paper(s)
Hybrid Management White Paper
Written by AvePoint
As organizations worldwide continue to look for ways to… (read more)