SQL Server Execution Plans



Any professional working with the SQL Server relational engine should be able to understand an execution plan to stop seeing the engine as a black box and be able to focus on taking advantage of it to get the maximum benefit.


Duration: 8 hours

Upcoming class

Date: December 12 – 13 Time: 16:00h – 20:00 CEST TIME

Course Goals

At the end of the seminar, the student will be able to:

  • Read execution plans
  • See a plan and understand if you need help from you
  • Understand the statistics and histograms of objects
  • What indices to create, if they make sense and why to make them
  • Understand whether a query is well formulated or can be improved
“It has given me knowledge to begin to understand the execution plans and thus to know how to improve my queries to the database, thus minimizing access and time in the consultations.”

WHERE and WHEN will this course be held?

In this course you will learn…

During this one-day course, you will learn how to read execution plans with SQL Server 2016. You will see the most frequent operators as well as a variety of queries and T-SQL solutions that have quite practical application in day to day.
Being able to read execution plans SQL Server gives the database developer the ability to decide whether the solution that SQL Server has encountered for query execution is optimal or if on the contrary we must modify something, whether query, indexing, plan guides, …to improve it.

Any professional working with the SQL Server relational engine should be able to understand an execution plan to stop seeing the engine as a black box and be able to focus on exploiting it to get the maximum benefit.

Who is this course for?

This course is designed for both DBA and developers working with SQL Server. The target audience is both the professional in charge of writing the required query and the DBA that manages the resources of the SQL Server instances and must decide if there are queries that are putting the performance of the system at risk.

Prerequisites: What do you need to know to complete the course?

  • This course is focused on professionals who have some experience with SQL Server
  • understanding of the relational model, as well as the T-SQL language

NOTE: Although the course is entirely about SQL Server 2014, it is not limited to professionals working with lower or higher versions since the important thing is to know how to read plans regardless of which version they are

Course Outline

Module 1: Introduction
SQL Server Management Studio
Module 2: Generation of execution plans
  • Query Optimizer
  • What is an Execution Plan?
  • Viewing an Execution Plan
  • Reading an Execution Plan
  • Display Modes
  • Plans Vs. Real
  • Operators
  • Tips
Module 3: Statistics
  • What are they
  • Properties
  • Updating statistics
  • Viewing statistics
  • How to read a histogram
  • Selectivity
  • Density
  • Cardinality
  • SARGs
  • Practical cases
  • Tips
Module 4 Operators
  • What are they
  • Logical Operators vs Physical Operators
  • Table Scan
  • NonClustered index scan
  • Clustered index scan
  • Index seek
  • Merge interval
  • Key lookup
  • Rid lookup
  • Spools
    • Eager/Lazy Spool
    • Row count spool
    • Table Spool
    • Nonclustered Index spool
  • Stream aggregate
  • Assert
  • Concatenation
  • Compute scalar
  • Segment
  • Sort
  • Paralell operators
    • Distributed streams
    • Repartition streams
    • Gather streams
  • JOIN operators
    • Nested loop
    • Hash join
    • Merge join
Module 5: Advanced
    • Using the Execution Plans Cache

    O Examine and manipulate

    • Forced parameterization
    • Optimzie adhoc for workloads
    • Parameter sniffing
    • Optimize for
    • Guided Plans
    • Patronization and bifurcation
    • Automatic recompilations

WHERE and WHEN will this course be held?

This course can be scheduled in more than one region. Please check availability in your country.