Understanding Graphical Execution Plans - Part 2: How to Create One

Being able to understand and analyze execution plans is an important and beneficial skill for SQL Server database administrators and developers alike. An execution plan documents the estimated cost of a query, the indexes used, and the operations performed. All of this information is vitally important in attempting to speed up a slow performing query.

This article is part of a three part series on graphical execution plans. Part 1 explains what execution plans are and discusses the differences between estimated and actual plans. Part 2 shows how to create both estimated and actual execution plans. Lastly, Part 3 takes a look at a simple graphical execution plan, and discusses several of the most common operators used in a query.

In order to analyze an execution plan, it must first be created. Both an estimated and an actual plan can be created graphically. See Part 1 of this series to learn the differences between estimated and actual plans. In context of creating a graphical execution plan, the primary considerations are that the actual plan provides some additional information that can be used in analyzing a query. The downside of creating an actaul plan is that the query must first be run to create it. For a long-running query, it might  not be worth the additional time involved in getting the actual plan. Read on to learn how to create both an estimated as well as an actual graphical execution plan.

How to Create a Plan on the AdventureWork2012 Database

There are four ways to create an estimated plan. For this example the following query will be used:

SELECT TOP 1000 [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[SpatialLocation]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Person].[Address]

Creating an Estimated Plan – Four Methods

There are four ways to create an estimated plan.

1. Click button on SQL Editor Toolbar (Figure 1)
2. Right-click in editor pane and  select “Display Estimated Execution Plan”  (Figure 1)
3. Ctrl+L
4. Under the Query menu,  select “Display Estimated Execution Plan” (Figure 2) 

Zoom in  |  Open in new window

 Figure 1: Creating an Estimated Plan

As shown above, either clicking the “Display Estimated Execution Plan” button on the SQL Editor Toolbar or right-clicking on the query and selecting “Display Estimated Execution Plan” will create an estimated plan.

Zoom in  |  Open in new window

Figure 2: Creating an Estimated Plan from Menu

An estimated plan can also be created by selecting “Display Estimated Execution Plan” under the Query menu or by pressing Ctrl + L.

Creating an Actual Plan

There are four ways to create an actual plan

1. Click button on SQL Editor Toolbar (Figure 3)
2. Right-click in editor pane and  select “Include Actual Execution Plan” (Figure 3)
3. Ctrl+M
4. Under the Query menu,  select “Include Actual Execution Plan” (Figure 4)

After completing any of the above steps, execute the query. The plan will be included in the bottom pane along with Results and Messages.

 

Figure 3: Creating an Actual Plan

As shown above, either clicking the “Include Actual Execution Plan” button on the SQL Editor Toolbar or right-clicking on the query and selecting “Include Actual Execution Plan” will create an actual plan when the query is executed.

Conclusion

Creating graphical execution plans is a simple process. SQL Server Management Studio provides several ways of creating both an estimated and an actual plan. Read part 3 of this series to learn how to read and analyze a graphical execution plan.

References                 

• Displaying Graphical Execution Plans (SQL Server Management Studio), TechNet Library - http://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx
• Fritchey, Grant (2008), SQL Server Execution Plans, Simple Talk Publishing *

 * The 2008 edition was used for this article, but a second edition was published in 2013.