Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

By Darren White,

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) 

 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.

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                 

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

Total article views: 7710 | Views in the last 30 days: 55
 
Related Articles
BLOG

Estimated rows, actual rows and execution count

It’s often said that a major discrepancy between estimated and actual row counts in a query’s execut...

FORUM

Estimation of Query Execution Time

Query Execution Time.

BLOG

Execution Plan Cost Estimates

It’s been emphasized over and over that the costs of operations within an execution plan, and the es...

BLOG

Querying Execution Plan XML For Inaccurate Row Count Estimates

I read this awesome post by Joe Sack, titled “Detecting Cardinality Estimate Issues with sys.dm_exec...

FORUM

Estimated number of rows - Temp tables

Does use of temp tables cause a difference in the estimated number of rows and the actual number of ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones