Execution Plan Formats in sql server

We can view the execution plan on three different formats.

Ø  Graphical plans

Ø  Text plans

Ø  XML plans

Graphical plans

Graphical plans are the most commonly used type of execution plan. They are quick and easy to read. We can view both estimated and actual execution plans in graphical format and the graphical structure makes understanding most plans very easy. However, the detailed data for the plan is hidden behind ToolTips and Property sheets.

See below example

When we move our mouse over icon we will get details.

Text plans

These can be quite difficult to read, but detailed information is immediately available. Their text format means that they we can copy or export them into text manipulation software such as Note Pad or Word, and then run searches against them. While the detail they provide is immediately available, there is less detail overall from the execution plan output in these types of plan, so they can be less useful than the other plan types.

There are three text plan formats

Ø  SHOWPLAN_ALL – A reasonably complete set of data showing the estimated execution plan for the query.

Ø  SHOWPLAN_TEXT – Provides a very limited set of data for use with tools like osql.exe. It, too, only shows the estimated execution plan

Ø  STATISTICS PROFILE – Similar to SHOWPLAN_ALL except it represents the data for the actual execution plan.

SHOWPLAN_ALL

A reasonably complete set of data showing the estimated execution plan for the query.

To see the execution plan in text we need to on and off the SHOWPLAN_ALL.

 See below example

Here I am calling a SP and want to see the execution plan.

USE TEST GO  SET         SHOWPLAN_ALL  ON;  GO  EXEC sp_GetTableData 'EMP' GO  SET SHOWPLAN_ALL OFF;  GO

I am executing this script.

In this plan we will get the all information.

SHOWPLAN_TEXT

It provides a very limited set of data for use with tools like osql.exe. It, too, only shows the estimated execution plan

To see the execution plan in text we need to on and off the SHOWPLAN_TEXT. See below example

Here I am calling a SP and want to see the execution plan.

USE TEST GO  SET         SHOWPLAN_TEXT  ON;  GO  EXEC sp_GetTableData 'EMP' GO  SET SHOWPLAN_TEXT OFF;  GO

I am executing this script

We can copy and pest it in note pad++

We can analyze our execution plans.

XML plans

XML plans present a complete set of data available on a plan, all on display in the structured XML format. The XML format is great for transmitting to other data professionals if we want help on an execution plan or need to share with co-workers. Using XQuery, we can also query the XML data directly. Every graphical execution plan is actually XML under the covers.

 There are two varieties of XML plan:

Ø  SHOWPLAN_XML – The plan generated by the optimizer prior to execution.

Ø  STATISTICS_XML – The XML format of the actual execution plan.

SHOWPLAN_XML

The SHOWPLAN option in SQL Server Management Studio has to be set using T-SQL and it shows the estimated execution plan. This is the same plan as shown when the Estimated Execution Plan option is selected, when the query is not actually executed.

USE TEST GO  SET         SHOWPLAN_XML  ON;  GO  EXEC sp_GetTableData 'EMP' GO  SET SHOWPLAN_XML OFF;  GO

Executing this script.

We can see this xml. Copy this and pest it on the Notepad++

It is very difficult to read and understand this execution plan.

If we click on the xml value it will redirect t

The graphical execution plans.