Administration · T-SQL coding

Execution Plan Basics

What is a SQL Server execution plan? Big topic: I suggest grabbing Grant Fritchey’s book SQL Server Execution Plans (A free eBook at the time of this post); 332 pages of good stuff on execution plans.  You can get it here:

Redgate link to Fritchey’s eBook

So you don’t want to read a whole book at this point?  What follows is a quick get started guide to execution plans (most of which is written by other people, but I’ll give them the credit where credit is due!).

Simply put, an execution plan is the path that the database engine will use to get the data you requested in your T-SQL statement.  As a relational database, you don’t tell the engine HOW to get your data, just WHAT data you want.  The query optimizer figures out HOW to get it; that’s the execution plan.  If you want to know a few more details I suggest reading this article, also by Grant Fritchey (if you don’t read his book above!):

Redgate link to Fritchey’s article on Execution Plan Basics

The beginning of this article is a great introduction to plans, then it dives a bit deeper for those who want to know more.

This next link is a good introduction to the graphical query plan, the different parts, and how to read the information presented.  It’s written by Tim Ford and only takes a few minutes to read:

How to read SQL Server graphical query execution plans

On a practical level, what do I need to look at (as a T-SQL developer) in an execution plan?  Each time I look at an execution plan I typically begin by looking at the same pieces of information to get a bird’s eye view of the query.  These items change in importance based on the query and the execution time, but I like to get all of these items on my radar before diving deeper or making any conclusions about the performance of the query.  These are the first 6 things that I look at in a plan to consider how the query is performing:

  1. Total Estimated Cost.  If the total cost is low, do I really need to make a change?  The answer is “maybe”, but I do look at the total cost first to get an idea of how much effort to put into performance tuning of the query.  Just hover over the left-most part of the plan for the statement (the SELECT operation in the example below) and the cumulative estimated cost of that step of the plan (including all steps to the right of the step) appears in the yellow box as “Estimated Subtree Cost”.

Plans Example 1

2.  Table Scans. If there are any table scan steps in the plan that can be a bad thing for your performance.  Scroll over the entire plan and see if there are any table scans (I see two scans in the example above).  This is where a knowledge of your database comes in handy.  Do any of the tables accessed contain a small number of rows?  If so, perhaps the query optimizer made a decision that a table scan would be faster than any other access.  Most likely, however, there is an index (a primary key, perhaps?) that could be added to help speed up your query and remove the pesky table scan.

3.  Sort Operations. Look for the Sort operator in the plan.  Sort can be one of the slowest operations in your plan.  Many times they are needed, but I take note of each one because you could possibly re-write part of your query to eliminate one or more sorts.  The example below has a sort operation caused by the ORDER BY clause.  Reviewing the actual plan may give you additional information on each sort operation.  If SQL Server needs to “spill” the sort over to TempDB then a warning will appear on the actual plan.  Spilling is really bad for consistent performance and should be reviewed to see if it can be eliminated.

Plans Example 2

4.  Missing Indexes. As shown in the example above, sometimes SQL Server will suggest that an index be added (see the green text in the plan above).  Hover over the green text and you can see the T-SQL to create the missing index, or right-click the green text and select Missing Index Details and the create index T-SQL will be placed in a new query window.  Note that the estimated impact (in percent) of adding the index is also shown (22.9% in this example).  It might be worth the effort to add the index (or modify an existing index); test it to determine if it is warranted.  You should make that judgement call based on the overall cost of the query, how often the query will be executed, and other factors.

5.  Key Lookups. The key lookup (RID lookup for heap tables) operation is an “extra” step that SQL Server must perform if the query accesses columns that are not found in the index used to retrieve the data.  While this is not necessarily a bad thing, it can be an easy step to eliminate from your execution plan by providing a covering index for the query.  The example below shows two lookup operations (since AdventureWorks doesn’t use primary keys the RID Lookup operation is used).

Plans Example 36.  Iterative Operations. This one is last on my list and probably the hardest item to detect and determine if it is a problem.  I call it an iterative operation; it is when SQL Server has to perform part of the query on a row-by-row basis instead of a set-based operation.  I look for operations that are executed multiple times in the plan.  In the example below you can see that the nested loop operation is executed 7 times.  This is a simple example and 7 executions is trivial.  However, my experience has been (for queries that may return thousands of rows) that this estimate is often wildly inaccurate and can cause significant performance issues with the query.  Quite often I find that a sub-select, a CTE, or the use of a function within a query causes this type of operation.  Perhaps the query could be re-written to remove the clause that is causing the iterative operation.

Plans Example 4

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s