Another document for the customer’s DBA checklist. This time we are going to look how we find and read an Execution Plan from a query.

Showing an Execution Plan

I will only focus on the graphical Execution Plans and in the examples the AdventureWorks2008R2 OLTP database is used which you can download here.

To see a Query Execution plan, you need to create a query:

SELECT * FROM Sales.vStoreWithContacts

And then you click the “Display Estimated Execution Plan” or the “Include Actual Execution Plan” button in the SQL Editor Toolbar of SQL Server Management Studio.

The difference between the Estimated and the Actual plan is the execution of the query itself. When you hit the “Display Estimated Execution Plan” button, SQL Server Management Studio will immediately show you an Execution plan, however it’s an estimated plan and the query itself is NOT executed. When you hit the “Include Actual Execution Plan” nothing will happen until you execute the query. After execution of the query a third tab will appear in SSMS showing the Execution Plan that was actually used:

Reading Execution Plans

When you want to read the above Execution Plan you have to read from right to left and from bottom to top. So SQL Server will start with

And end with

Other information you directly can see is the most expensive operation and based on the thickness of the connecting arrows you can see the amount of data that�s streaming through them:

Finding more information

When you hover your mouse over the arrows or operations in an Execution Plan a pop-up will appear giving you more detailed information about the rows or operation:

Needless to say that the Actual Number of Rows and some other information will not be available in an Estimated Execution Plan. In the example above we see a big difference between the Estimated and the Actual Number of Rows which might indicate that our statistics are out of date.

For even more information select an operation and open the Properties Window (F4):

Another option to get more out of your Execution Plans is to download SQL Sentry Plan Explorer more information is found at their site but here is a screenshot from the same Execution Plan in the Free Plan Explorer:

As you can see, the most expensive operation is highlighted in red and the tool also highlights the difference between the estimated and actual rows.

Missing index information

When executing the following query:

SELECT * FROM Sales.vStoreWithContacts
WHERE Firstname = 'Alan'

You’ll see some green text in the top of the Execution Plan:

This information comes from the Dynamic Management View: sys.dm_db_missing_index_details. More information on Indexes is out of scope for this post but be careful with just implanting these Missing Indexes. They might improve the performance of this query but since they need to be maintained they can also slow down other operations on the database.

Comparing Execution Plans

Are you rewriting a query and wondering which one should perform better? You can easily compare the Execution Plans of two queries. Just write these queries in one query window:

SELECT * FROM Sales.vStoreWithContacts
WHERE Firstname NOT LIKE 'A%';

select * FROM Sales.vStoreWithContacts
WHERE FirstName IN (SELECT FirstName FROM Sales.vStoreWithContacts
WHERE FirstName NOT LIKE 'A%');
GO

When you execute the queries you’ll see they both return the same 70 rows but when you look at the header of the Execution Plans you’ll see that the first query costs less than the second one in this batch:

Conclusion

Execution Plans gives us valuable information and a deep insight in how SQL Server executes the queries we write. Reading and understanding Execution Plans are essential for writing well performing queries.