You won't solve SQL performance problems by reading Execution Plans alone
One of the first things that DBAs tend to do when investigating a query performance tuning problem is to check out the query's execution plan, preferably via the graphical showplan in QA or SSMS. Sure, this tells you how the query is currently being executed within the engine & can even provide you with valuable insight into where the inefficiencies are within the current plan but this is is very different from actually telling you how to SOLVE the problem. I've seen it many times - DBAs (including me) glaring at Execution Plans, looking for insights into "why" a query is running slow, but not getting any "answers".
A new DBA joined our team recently & I've enjoyed having some interesting discussions with him about how to approach query tuning, which prompted me to make this point here - it's crucial to understand SQL Server's query processing capabilities if you want to be effective at query tuning. Without having an understanding of what's "possible" in any query scenario, it's not possible to know what you should expect, performance-wise, from that query. Without forming this expecation, you really have little to judge whether your tuning work has been successful or not - & therefore whether your job is actually complete or not.
Our particular discussion this week centered around why merge join operators can sometimes be the most efficient techniques for joining tables, even when the available indexes don't at first appear to be useful for merging. Without this particular knowledge, we mght have been satisfied that nested loops were the best option for the specific query we were looking at. But by using our knowledge of what we could expect from SQL Server's query processing capabilities, we formed a picture of what execution plan we should expect from SQL Server.
We designed the indexes we felt were necessary to support the query & then used the Showplan to confirm that SQL Server was doing what we expected.
My point here is that you won't finid the answers to solving bad query performance by looking at execution plans alone. Sure, the Database Tuning Advisor or Index Tuning Wizard can help in relatively simple scenarios, but nothing beats developing an understanding of how SQL Server processes queries & forming an idea for yourself about what you expect.before embarking on a tuning exercise. Once you've made it this far, resolution of the problem shouldn't be far away..