Execution Plan

Discussion in 'SQL Server' started by MinalS, Apr 21, 2016.

  1. 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:

    [​IMG]

    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:

    [​IMG]

    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:

    [​IMG]
    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.
     
    Last edited by a moderator: Jan 21, 2017
    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice