3.1.Obtain Query Execution Plan

1. Introduction

• In SQL server, we can get some useful information to help us to optimize efficiency of our SQL query from Query Execution Plan,

  such as: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?

2.Types Query Execution Plan

• 1.Estimated execution plan

  • the plan that represents the output from the optimizer

• 2.Actual execution plan

  • the plan that represents the output from the actual query execution

3.Get started

• 1.Push Include Actual Execution Plan button or Ctrl + M before execute SQL query

• Or push Display Estimated Execution Plan button or Ctrl + L before execute SQL query

• 2.Execute SQL query by push F5

• 3.After SQL query finished, we can obtain a report below

  

4.ToolTips

• 1.SELECT operator

  • (1) Cached plan size (記憶體大小)

    • [cache performance issues]: how much memory the plan generated by this query will take up in stored procedure cache.

  • (2) Estimated Operator Cost

  • (3) Estimated Subtree Cost (累積至目前的accumulated optimizer cost大小)

    • tells us the accumulated optimizer cost assigned to this step and all previous steps, but remember to read from right to left

  • (4) Estimated number of rows

    

• 2.Table Scan

  • (1) Estimated Operator Cost/ Estimated Subtree Cost

    • the operator cost and the subtree cost are the same, since the table scan is the only operator. (如果有多個operator, 每一個operator個別的cost將會加總到total)

  • (2) Estimated number of rows

    • Change as various operators perform their work on the data as it passes between each operator (不同的operator, Estimated number of rows也會不同)

  • (3) Ordered

    • whether or not the data that this operator is working with is in an ordered state.(此operator是否會使data呈現排序狀態, 是否是ordered可以讓我們知道哪裡可能有多餘的處理, 以讓data進入排序狀態, 像是order by)

      

5.Operator Properties

• Properties has more information than Tool Tip

• Follow steps bellow:

  • 1.Right click Table Scan and select Property

    

  • 2.Check the table on the right side:

    • Defined Values

      • Estimated Rebinds /Estimated Rewinds

        • The number of times an Init() operator is called in the plan

      • Forced Index

      • NoExpandHint

    • Object

    • Output List property

      

6.Reference

• https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/