SQL Server Query Execution Plan

Factual Questions
#1

What I do is design and program ETL processes. Large chunk of my time is spent writing Stored Procedures. I really don’t understand a lot about the query execution plan. I can pretty much do 3 things: 1) I can look for bottle necks (why is 90% of the resource usage on this one transaction?), 2) I can look for unexpected behavior (why is it doing a table scan instead of an index scan?), 3) I can compare two queries . Unfortunately, I don’t feel confident that I am even doing these 3 things right. Also, I can’t help but feel that there is much more information there that I just don’t know how to understand.

So, on to the questions. What is the execution plan good for? Do most people writing T-SQL make use of this? Where can I go to learn more? Is there a good book? A website?

Please don’t say to ask the DBA. The jr. DBA is essentially as knowledgeable as me. I asked the Sr. DBA and he said that he doesn’t really use it. The only person I know that uses it is the DBA at my previous company. We used to have a monthly technology brown bag lunch. I talked him into giving a class for one, but I changed jobs before he did.

Any thought or advice is appreciated!

#2

Execution plan can be used for perf tuning just like you are doing.

If I’m fixing someone elses problem, then I might use it (if the problem isn’t obvious, I’m unfamiliar with the query and it’s complex), but for my own queries I typically know in advance how to construct it for good performance, so I rarely use it in those cases.

I don’t have any recommendations for books or websites other than the fact that there are tons of websites on just about any technical topic, you should be able to google it (e.g. “sql server using execution plan for performance tuning”)