Database
  • Introduction
  • Chapter1: MySQL
    • 1.1. MySQL在Max OSX上的筆記
      • 1.1.1.安裝My SQL (Mac OS X)
      • 1.1.2.My SQL在Mac上的一些warning message
        • 1.1.2.1.更改密碼
    • 1.2.MySQL for Beginners
      • 1.2.1.Getting the Development Environment Ready
        • 1.2.1.1.Windows console
      • 1.2.2.Beginning with MySQL Basics
        • 1.2.2.1.Creating a Database - CREATE DATABASE
        • 1.2.2.2.Creating a Table - CREATE TABLE
        • 1.2.2.3.Inserting Values into Table - INSERT INTO
        • 1.2.2.4.Retrieving Values from Table - SELECT
        • 1.2.2.5.Selecting Distinct Values from Column - DISTINCT
        • 1.2.2.6.Selecting a Specific Data using WHERE Clause
        • 1.2.2.7.Selecting with Multiple Identifiers Using AND
        • 1.2.2.8.Selecting with One of Many Identifiers Using OR
        • 1.2.2.9.Sorting the Data Using ORDER BY
        • 1.2.2.10.Limiting the Data Retrieved Using LIMIT
        • 1.2.2.11.Updating or Modifying Data in a Table Using UPDATE
        • 1.2.2.12.Deleting a Data from a Table Using DELETE
        • 1.2.2.13.Copy table
        • 1.2.2.14.Deleting a Table - DROP TABLE
        • 1.2.2.15.Deleting all values in Table - TRUNCATE TABLE
        • 1.2.2.16.Altering Table Structure - ALTER TABLE
      • 1.2.3.Time to Jump in for More
        • 1.2.3.1.Selecting Data with a Range - BETWEEN
        • 1.2.3.2.Creating Alias Name for a Column - AS
        • 1.2.3.3.Selecting Data which has one of Multitple Values - IN
        • 1.2.3.4.Using LIKE and WILDCARDS
        • 1.2.3.5.Copy Table with INSERT INTO SELECT
        • 1.2.3.6.Combine the Tables - UNION
      • 1.2.4.Few things that Keeps the Database in Shape
        • 1.2.4.1.Primary key
        • 1.2.4.2.Unique key
        • 1.2.4.3.Foreign key
        • 1.2.4.4.Default value
      • 1.2.5.Using Joins to Combine Tables
        • 1.2.5.1.Inner join
        • 1.2.5.2.Left join
        • 1.2.5.3.Right join
        • 1.2.5.4.Full join
  • Chapter2: MongoDB
  • Chapter3: SQL Server
    • 3.1.Obtain Query Execution Plan
    • 3.2.BCP
    • 3.3.SQL操作
      • 3.3.1.取代欄位, 更改欄位中的值
    • 3.4.產生create或drop database/table的script
Powered by GitBook
On this page
  • 1. Introduction
  • 2.Types Query Execution Plan
  • 3.Get started
  • 4.ToolTips
  • 5.Operator Properties
  • 6.Reference

Was this helpful?

  1. Chapter3: SQL Server

3.1.Obtain Query Execution Plan

PreviousChapter3: SQL ServerNext3.2.BCP

Last updated 5 years ago

Was this helpful?

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/