Guide to reading, comparing, and interpretting Execution Plans in SQL Server

Question

I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others.

If you want to add your own summary here that would be welcome, though I expect the topic is large enough that links to pre-written articles will be required. The more the merrier.

Update:
After much searching, I found this question as well:
http://stackoverflow.com/questions/333383/any-guides-on-learning-how-to-interpret-a-sql-querys-execution-plan-in-sql-serve/333410

However, that only has a single answer and it points to 181 page pdf file. While I will go through that, I'm looking for more of a guide; not a reference manual.

Answer 1

This looks like a very comprehensive article.

Answer 2

Reading & Comparing execution plans is a big topic. Let me add some basic information. First how do we get the execution plan? There are a couple of ways each with different benefits:

• Query Analyzer (SSMS) -> Estimated Execution Plan - This gives you a graphical representation of an estimated execution plan. This is not always accurate but is useful when you can't run the query you want to analyze for example if the query takes to long.

  • Query Analyzer (SSMS) -> Actual Execution Plan - This will give you a graphical representation of the actual exection plan. This is useful when you want to test an isolated query.

  • SQL Server Profiler - Include Execution Statistics - This will let you get a text / graphical representation depending on your sql server version, of all statements running on SQL. If you want to monitor a production system, this is what I would use as you can see everything that is going on. Make sure to include binary data.

In general when optimizing my queries I would look for the following signs:

• Are there any Index or worse Table Scans going on if so you probally could use a new index.
• Are there any operations which are executing with a very large number of executions.
• Are there any bookmark operations that are taking significant time, If so a covered index or including additional columns in SQL 2005+ might help here.
• Are there any missing statistics errors.

Answer 3

Hi Joel,

The funny thing is, that is considered a guide to execution plans, rather than a complete manual. I can say first hand that the reference mentioned is worth the effort and time to read. You need to become quite profficient with the topic of execution plans and how the SQL Server database storage engine works under the hood, in order to be able to deliver results from the knowledge.