Mastering The Basics Of Sql Server Query Optimization

Learn to Create Optimal Execution Plans for Your Queries

Last updated 2022-01-10 | 4.3

- At the conclusion of this course you'll understand how the optimizer works at a very high level.
- You will be able to read showplans. These are created when a query is executed.
- You'll understand the caching mechanisms behind SQL Server optimizer.

What you'll learn

At the conclusion of this course you'll understand how the optimizer works at a very high level.

You will be able to read showplans. These are created when a query is executed.

You'll understand the caching mechanisms behind SQL Server optimizer.

* Requirements

* This course was designed for anyone with a working knowledge of relational databases
* specifically SQL Server.
* It can be anything other than Express also
* Other than SQL Server nothing else is needed to take the course.
* I've included code samples for the course in the Course Download lecture.
* You will need to download and install SQL Server 2012
* 2014 or 2016 Express Edition
* You'll also need to download a copy of the Adventureworks database.

Description

Student Warning: Even though this is a beginners class it's fast paced and will require some outside study on your part.

I've included 4 different preview lessons so you could better gauge the speed of the course. Please take a moment to preview all of them. If those lessons are too fast then please take this course after you've taken my entry level courses. Thanks.... Mike

Recent Unsolicited Testimonials

“Learned lots from this well constructed and easy to follow course - thanks Mike!” – Caroline

“Feel much more confident in MSSQL now. Thanks!” Jef Yo

“This short course is excellent and will give you a good understanding of the basics in SQL server administration.” Luke Dunn

In this course we are going to delve into the Query Optimizer, easily one of the most complicated pieces of software ever written.

The Query Optimizer is the SQL Server component that tries to give you an optimal execution plan for your queries. The Query Optimizer tries to find that execution plan as quickly as possible. Not the perfect plan but a good one.

The SQL Server Query Optimizer is a cost-based optimizer. It analyzes a number of execution plans for a given query, estimates the cost of each of these plans, and chooses the plan with the lowest cost.

The optimizer seeks to find balance between the optimization time and the quality of the selected plan.

The course will be a balance of hands on query performance tuning and understanding the basics of the optimizer’s architecture.

We are going to learn about the execution engine, statistics and cost estimation, index selection and the optimization process.

At the core of the SQL Server Database Engine are two major components: the storage engine and the query processor also called the relational engine.

In this course our sole focus will be on the query processor. This part of the engine accepts all queries submitted to SQL Server and creates a plan for optimal execution.

See you in the course!!!

Who this course is for:

This is a mid level course aimed at those who have a working foundation of SQL Server.
This is not an entry level course.
Everything you need to take the course will be included. There are no items you'll have to purchase.

Course content

5 sections • 48 lectures

Introduction - How the Query Optimizer Works 12 lectures

An Introduction to This Course Preview 01:26

What are we going to cover in this course.

Please take a quick perusal of this and other lectures to get a clearer idea on what's we will be covering in the course.

Is This Course Right for You? Preview 00:59

In this lecture I want to make sure you are in the right place.

This course is about the Query Optimizer and about the basics of reading execution plans.

Course Downloads Preview 00:11

The SQL Engine Preview 01:16

The SQL Server Engine has two major componets.

In this lecture let's find out what they are.

The Query Processing Process Preview 01:30

When any query is issued in SQL Server a plan is created.

This plan is created via a variety of steps.

Let's talk about them in this lecture.

The Search Space Preview 01:44

In this lecture we will define what a Search Space is.

What's In the Cost? Preview 01:32

How much does it cost to do all this?

Let's find out.

Plan Cache Reuse Preview 01:20

As a general rule we want plan cache reuse.

It's a good thing.

Let's talk about it in this lecture.

What Causes Recompilation? Preview 00:51

Some times new plans will have to be created.

What are a few of the major causes of recompiles?

Terminology Preview 04:02

Let's cover the new vernacular in video format.

Summary Preview 01:19

Let's wrap up what we've covered in this section.

Quiz

Execution Plans 17 lectures

Brain Overload Warning Preview 00:41

There is a lot new vernacular for the student starting in execution plan analysis.

Don't become frustrated or rush through the course.

This will take time.

Anatomy of Execution Plan Preview 02:34

In this lecture let's learn about the basic parts and terminology of an execution plan.

The Iterator Preview 00:30

Why are operators called iterators?

Let's find out why in this lecture.

Execution Plan Output Options Preview 01:37

There are a variety of methods we can use to look at execution plans.

However, the vast majority use the right click approach to view them pictorially.

Scan VS Seek Preview 01:56

The two most fundamental access methods in SQL Server.

Let's define them.

Scanning Demos Preview 02:09

The word scan in database parlance means bad.

Let's see some scans in action and learn how to spot them in this lecture.

Seeking Demos Preview 03:06

The "seek" is the most efficient way we can navigate our structures.

Let's take a look at exactly what that is in this lecture.

The Bookmark Lookup Preview 04:08

One of the most common kinds of problem areas in real world plans.

Let's define them and correct them.

Aggregations Preview 02:02

Aggregations aren't difficult but there are some nuances with the operators.

Let's cover them in this lecture.

Hash Match Operator Preview 03:05

What's a Hast Match operator?

In this lecture we will find out.

Joins - The Nested Loop Preview 01:54

SQL Server uses to implement logical joins: the Nested Loops Join, the Merge Join and the Hash Join.

It is important to understand that no join algorithm is better than the others, and that the Query Optimizer will select the best join algorithm.

Join - The Merge Join Preview 01:25

The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate.

Let's look at an example with a merge join.

Joins - The Hash Join Preview 01:17

Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference.

Let's learn about Hash Joins in this lecture.

Parallelism Introduction Preview 01:13

Parallelism is a complicated topic.

Let's define it at a very high level in this lecture.

Terminology Preview 01:36

There are lots of new terms in this lecture.

You many want to print them out.

Summary Preview 00:57

Let's look at the high points of this section.

Quiz

Cost Estimation 10 lectures

Statistics Contain 3 Key Pieces of Information in DBCC SHOW_STATISTICS Preview 01:35

Statistics refers to the statistical information about the distribution of values in one or more columns of a table or an index.

There are three key sections of the output for learning about statistics.

We will cover them in this lecture.

Quick Review Preview 00:30

Let's pause for one second and keep several critical aspects of learning about statistics in mind.

Anatomy of a Statistic (Header,Vector and Histogram) Preview 04:24

There are different ways you can view the details of the statistics objects. You can use the DBCC SHOW_STATISTICS command. DBCC SHOW_STATISTICS shows the header, histogram, and density vector based on data stored in the statistics object.