FORMATS OF EXECUTION PLAN & USAGE PERMISSION
2.SQL SERVER offers three formats for execution plans. They are
c. XML Plans
2.Graphical Plans are easy to read but the detailed data for the
plan is masked. Both Estimated and Actual execution plans can be viewed
in graphical format.
3.Text plans provides three text plan formats:
a.SHOWPLAN_ALL : a reasonably complete set of data showing the
Estimated execution plan for the query.It will not give u actual
b.SHOWPLAN_TEXT : provides a very limited set of data for use with
tools like osql.exe . It will only give you estimated execution plan.
c.STATISTICS PROFILE: similar to SHOWPLAN_ALL except it represents the data for the Actual execution plan
4.XML plans present the most complete set of data available on a
plan, all on display in the structured XML format. There are two
varieties of XML plan:
a.SHOWPLAN_XML : The plan generated by the optimizer prior to
execution.SQL Server returns detailed information about how the
statements are going to be executed in the form of a well-defined XML
b.STATISTICS_XML : The XML format of the Actual execution plan.
Permissions Required to View Execution Plans : GRANT SHOWPLAN TO [user]
Examples are given below.
SET SHOWPLAN_ALL ;
SET STATISTICS PROFILE ON
SET SHOWPLAN_XML ON