SQL Server Execution Plans AdvancedIf 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:
• 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
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”
Module 1: Generation of execution plans
- Query Optimizer
- What is an implementation plan?
- Differences between reading and executing a plan
Module 2: Statistics
- Updating statistics
- Viewing statistics
- Selectivity, density and cardinality
- Practical cases
Module 3: Operators
- What are they and how do they work?
- Review of the most important
- JOIN (HASH, MERGE , LOOP)
- 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
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.