question

iamgoat avatar image
iamgoat asked

How do you read / understand an execution plan?

It's a very powerful feature in MS SQL Server Management Studio, but I've always been lost when it comes to understanding what it's trying to tell me about a query.

sql-serverssmsexecution-planprofiler
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Grant Fitchey has a great book on this - you can get an ebook version over at SQLServerCentral.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

Edit: expanded list to included covering vs noncovering indexes

Do you mean the execution plan?

The plan shows how SQL Server will execute your query. The main benefit is to see what indexes are used (or not used). Here's a basic list (best to worst) of some methods used to find data:

  1. covering index seek
  2. noncovering clustered index seek
  3. noncovering index seek
  4. covering index scan
  5. noncovering clustered index scan
  6. noncovering index scan
  7. table scan

The actual performance of a covering index scan would fall somewhere after a covering index seek but before any noncovering index scan. It would depend on the table size, index sizes, index column data types, etc. The difference between seeks and scans increases as the number of rows in the table/index increases.

Side note: In SQL Server 2008, you can create filtered indexes that can also improve performance by decreasing the number of rows in the index.

If you see the plan using one of the later methods, see if perhaps your query can be changed to use a better index. It's also possible that you might need to create a new index to improve performance.

Sorry. It's been a long day, and I'm tired, so that's all I've got right now.

If you want more information, here's a good review I just found: http://www.sqlservercentral.com/articles/Administration/executionplans/1345/

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

Hi Goat....

To read a plan, start with the window closed. Work out how you'd solve the problem in an ideal, but paper-based, world.

Then go to the plan, start at the top row, on the right, and work in. Look at the size of the arrows, and see how the system is solving your query.

Learn about the different join methods (merge,hash,loop) to know to interpret branches. Also, whenever you see a COMPUTE SCALAR, look inside to see if it's calling a function. If it is, then watch out because the effort to work out the value of that function isn't included in the cost of your query.

Make sure you understand about Lookups. If the plan uses a non-clustered index (NCIX), but the NCIX doesn't have all the data you need, it will need to do a Lookup to get the rest. This is like a CIX Seek for each row. Look at the size of the arrow to see how many rows it needed to do. You may well find that a Scan over the whole table would've been better.

Could go on for hours (or pages), but this will be a useful start. Make sure you think about how you'd solve the query, and then look at the plan to see if the Query Optimizer agreed with you.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

This is a huge topic. Here are some high points that I always try to emphasize:

Remember that there are two types of execution plans, estimated and actual. They're usually the same, but not always. The data supplied between the two is different in a few important points, the number of executions for a given operator, the actual number of rows moved, and the actual cpu cost for the operator.

Read graphical execution plans from the top right, working backwards.

The cost estimates on display for each of the operators are just that, estimates. They're estimates in the actual plan too. They are not reliable as a means for measuring performance differences between two plans, but they're one of the few measurements available.

The thicker the connecting arrow between two operators, the more data that is being moved.

Performance points to look for. None of these are carved in stone and there are always exceptions:

  • Scans against tables or indexes instead of seeks
  • Merge joins instead of hash or loop joins
  • Disparity between estimated number of rows and actual number of rows
  • Data sorting or ordering operations when you don't have an ORDER BY in the query

Check the properties on the final (first listed) operator to see if the execution plan is the best plan found or the result of an optimizer timeout. If it's a timeout, you may have tuning or tweaking opportunities that are not readily apparent. You also may need to apply hints to arrive at an optimal plan.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

Toms answer covers a lot of it and I just want to add some points. Take a look at the estimated or actual cost of the steps, number of executes and the cost in percent. Even if cost is not an exact number, it will give you something to compare different solutions with. And with the percentage you can pinpoint the heavy parts of your query.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.