SQL Server Execution Plans Advanced

If you already know the basic techniques of reading execution plans, in this course you will learn new techniques to take advantage of and scenarios not seen in the basic course.


Duration 8 hours

Course Goals

At the end of the course, the student will be able to:
• Differences between Row and Batch runtime
• Recommended storage models based on data usage (RowStore vs ColumnarStore)
• Optimization techniques in parallel scenarios
• Where and how to use SQLCLR correctly
• Use of XEvents together with execution plans for real cases

“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?

All courses can be delivered privately and customized to meet the requirements of your company.

In this course you will learn…

This 1-day course is designed for students in the course of “SQL Server Execution Plans” who want to strengthen their knowledge and to know more in depth the execution plans with SQL Server.
In this edition, after a brief review on the reading of execution plans, we will see optimizations with SQLCLR, practical application of QueryStore, parallel execution plans, batch mode and operations with columnar storage, engine optimizations we have with SQL Server 2017, …
If you already know the basic techniques of reading execution plans, in this seminar you will learn new techniques to take advantage of and scenarios not seen in the basic seminar.

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. In addition, this seminar is designed for professionals who have attended the course “SQL Server Execution Plans”.

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

  • This course is focused on professionals who have some experience with implementation plans
    • Know the relational model, as well as the T-SQL language
    • It is preferable to have previously attended the course ” SQL Server Execution Plans”
NOTE: Course focused on SQL Server 2016 and 2017. It is not limited to professionals working with earlier versions, but we will see some techniques and optimizations explicitly thought for versions 2016 onwards.

Course Agenda

Module 1: Generation of execution plans
  • Query Optimizer
  • What is an implementation plan?
  • Differences between reading and executing a plan
Module 2: Statistics
  • Properties
  • Updating statistics
  • Viewing statistics
  • Selectivity, density and cardinality
  • Practical cases
  • Tips
Module 3: Operators
  • What are they and how do they work?
  • Review of the most important
    • Spool
    • Sort
  • Aggregate
  • Parallel operators
  • Collumnar operators
Module 4: Practical cases
  • Temporal tables vs. table variables vs. in-memory tables
  • Views and common table expressions
  • Cache of execution plans
  • Parameter sniffing
  • QueryStore Applications
  • Use of Xevents to capture plans and optimize the real scenario
  • Waitstats and execution plans
  • Functions and execution plans
  • Optimization with CLR
  • Improvements to SQL Server 2017
Temporary breakdown

NOTE: Note that of the 8 hours of the course, there is only 40 minutes of material already seen in the basic course as a review.

WHERE and WHEN will this course be held?

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