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.
