SQL:Show Execution Plan of SQL Statement--SHOWPLAN_ALL

--show the execution plan of any statement --set Showplan_all OFF OR ShowPlan_text ON--Then Run your query check and set the Showplan to OFF
--Syntax
SET SHOWPLAN_ALL { ON OFF }
--Example
SET SHOWPLAN_ALL ON--Table is not created ,only Execution plan of the 'Create Table' type will displayCREATE TABLE TEST_TABLE(T_ID INT,T_NAME VARCHAR(50))
--Set the SHOWPLAN_ALL OFF SET SHOWPLAN_ALL OFF
--Table CreatedCREATE TABLE TEST_TABLE(T_ID INT,T_NAME VARCHAR(50))
Select Statement:
--On StatusSET SHOWPLAN_ALL ONSELECT * FROM TEST_TABLE
--Off StatusSET SHOWPLAN_ALL OFFSELECT * FROM TEST_TABLE

This Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resourcerequirements for the statements.
When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, informationabout all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table, informing users that the specifiedtable does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.SET SHOWPLAN_ALL is intended to be usedby applications written to handle its output. Use SET SHOWPLAN_TEXT to return readable output for MicrosoftWin32 command prompt applications, such as the osql utility.SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified inside a stored procedure; they must be the only statements in a batch.With SHOWPLAN_ALL statement:-
Parallel
0 = Operator is not running in parallel.(Not Sucessfully Executed)
1 = Operator is running in parallel (Sucessfully Executed)
SET SHOWPLAN_ALL returns information as a set of rows that form a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement. Each statement reflected in the output contains a single row with the text of the statement, followed by several rows with the details of the execution steps. The table shows the columns that the output contains.Also follow SHOWPLAN_TEXT & SHOWPLAN_XML.

Posted By: Manoj K. Bardhan
Follow Link-http://javadevelopersguide.blogspot.com