Execution Plan

Complete details regarding the query process can be viewed using the execution plan. The execution of query through the SQL Server can be viewed using the plan. The troubleshooting method is provided for the query which has slow processing.

Once the query is sent to the server, a parse tree is generated for the query. The logical steps for the query are described. The optimizer processes the tree and creates the execution plan for the query. The plan contains CPU time, cost of the plan, etc. Once the plan is created, query executes and the result is created.

The following formats are provided by the execution plan.

1. Text plan
2. Graphical plan
3. XML plan

Text plan

The execution plan does not display complete information at a moment of time. It is necessary for the user to display the information in a text format. The execution plan is represented in the hierarchical tree format. A single display is used to show all the information.

The different T – SQL statements used for enabling the query execution plan are as stated below:

Code:

SET SHOWPLAN_ALL ON

Using the above command, user can activate the text execution plan. The data for all the related SQL statements is accessed. Once the information is visible, user needs to turn OFF the showplan. If not done, all the DML statements are not executed.

Code:

SET SHOWPLAN_ALL OFF

Set the SHOWPLAN_ALL ON. Next, execute the following query for displaying the text plan.

Code:

Select * from orderdata

The text execution plan for the above query is:



The execution plan in text format is visible. The statement which was submitted to the server is displayed in the first column. The operations performed on the table are displayed in the following rows. The physical operations and logical operations performed are displayed using PhysicalOp and LogicalOp.

Graphical plan

The detailed analysis of the execution plan is useful in making appropriate decisions. The execution plan contains values like I/O cost, row size, query optimizer, etc. The graphical execution plan provides the information in tree format. An icon is used for representing the node of the structure. The physical or logical operators used for executing the query or statement are defined. The plan can be viewed in the window in one of the following ways:

1. Select the query from the query editor window, click Displaying Estimated Execution Plan button
2. Right click the query and select include action execution plan option
3. Select Include Action execution plan option from the SQL editor toolbar

Execute the following query to display the execution plan.

Code:

select o.orderid, o.quantity, p.productid, p.productname from orderdata o join productdata p on o.quantity = p.quantity 

The output generated when the query is executed is as shown below:

XML plan

The text plan cannot be transferred to other devices where the SQL server is not available. For such condition, we use the XML plan for better portability across different platforms. The need for installation of the server on the remote machine is not mandatory.

The display for the XML plan is in XML format. User can add XPath and Xquery for displaying data. The XML plan is executed using the following statement.

Code:

SET SHOWPLAN_XML ON

Execute the following query in the system.

Code:

select * from productdata

The XML plan for the above query is generated as shown below:


For turning the XML plan off, execute the following command.

Code:

SET SHOWPLAN_XML OFF

Control of execution plan

The role of the query optimizer is execution plan using the parse tree. If the performance of the query is not optimum, the optimizer does not provide good results. More CPU time is needed for execution of the query. The hints are provided to the optimizer for proper execution of the query. The hints override the plan used by the optimizer of query execution.

The hints provided by the SQL server are as listed below:

1. Join hints - The join is applied between the tables. The execution of the query can be restricted using the join type.
2. Table hints - The table hints are used for overwriting the default behavior of the locking. Once user executes the T- SQL statements, the locks are added on the tables.
3. Query hints - Used for adding a specific logic to complete query. The database engine removes the execution plan created for the query and recompiles the plan for the same query.