Execution Plans

So, this is the first part of the series on how to read an execution plan. In this post, I’m going to give a high-level overview of how the execution plan looks and how, in general, to read it.

For the purposes of this and other posts in this series, all screenshots will be from SQL Management Studio (the 2005 GUI) and all comments on features will refer to that tool. Query Analyser, from SQL 2000, is very similar. For other querying tools, your mileage may vary.

Here’s a very simple execution plan. The query that produced this plan is just a join of three tables in the AdventureWorks database on SQL 2005.

(more…)

One of the more common questions I get from colleagues is on how to read the execution plan that SQL produces. At first I found it quite a hard question to answer as no one really taught me how to read them. When I started with performance tuning I got a bit thrown in the deep end.

This is going to be the first in a series, maybe short, maybe long, on reading SQL’s execution plans. I’m going to go into the overall structure of a plan; the differences between the actual and the estimated plans, and how to obtain both; and some of the more common query operators and how they affect query performance.

If you have any specific questions that you’d like answering regarding execution plans, please post a comment here. Otherwise I’m going to ramble on a bit touching on things that I feel are important and relevant and maybe missing things that you would like to know.

Current articles in this series, in order of writing, are

1. Structure of an execution plan
2. Execution plans, estimated vs actual
3. Execution plans from Profiler
4. A basic execution plan
5. Execution plan operations – scans and seeks
6. Execution plan operations – joins
7. Execution plan operations – aggregate
8. Execution plan operations – misc
9. Execution plans – important properties
10. Execution plan – more properties
11. An example execution plan

There is more to come.

I was asked a question about this recently, so I thought I’d start this blog off with a brief overview of SQL Server’s execution plans.

Simply put, a query’s execution plan is the query translated into a hierarchical set of operators that the query execution engine understands. The query optimiser is responsible for producing this plan.

A query can have a number of different possible execution plans, depending on the resources available on the server, the amount of data in the source tables, the load on the server.

(more…)