Global - (+34) 91 414 89 50 | N. America - (800) 757 6543 contact@solidq.com

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

At the end of the course, the student will be able to difference between Row and Batch runtime, to choose models based on data usage (RowStore vs ColumnarStore), how to apply optimization techniques in parallel scenarios, where and how to use SQLCLR correctly and the use of XEvents together with execution plans for real cases.

 

If you’re looking for a basic course, check out >> SQL Server Execution Plans

COURSE DELIVERY OPTIONS

1-DAY CLASSROOM

The course will take place in a classroom with no more than 20 students in order to maintain a good level of interactivity.

PRIVATE ONSITE

The course will take place in your company’s facilities. We limit attendance to no more than 20 students in order to maintain a good level of interactivity. Request quote here.

Course Benefits

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

Expert Mentors

Our instructors have faced in previous real case projects, the same problems you are facing now. Learn from experience professionals.

t

Interaction and Q&A

In all of our trainings, you will have the chance to ask individual questions and be capable of solving certain issues.

Course Coverage

+

Differences between Row and Batch runtime

N

Where and how to use SQLCLR correctly

Recommended storage models based on data usage (RowStore vs ColumnarStore)

Use of XEvents together with execution plans for real cases

Optimization techniques in parallel scenarios

“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.”

COURSE OUTLINE

Module 01: Generation of execution plans

  • Query Optimizer
  • What is an implementation plan?
  • Differences between reading and executing a plan

Module 02: Statistics

  • Properties
  • Updating statistics
  • Viewing statistics
  • Selectivity, density and cardinality
  • Practical cases
  • Tips

Module 03: Operators

  • What are they and how do they work?
  • Review of the most important
    • JOIN (HASH, MERGE , LOOP)
    • Spool
    • Sort
  • Aggregate
  • Parallel operators
  • Collumnar operators

Module 04: 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

Would you like to register for this course?

Fill out the form at the top of this page