Mastering the Basics of SQL Server Query Optimization
4.1 (57 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
520 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Mastering the Basics of SQL Server Query Optimization to your Wishlist.

Add to Wishlist

Mastering the Basics of SQL Server Query Optimization

Learn to Create Optimal Execution Plans for Your Queries
4.1 (57 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
520 students enrolled
Created by Mike West
Last updated 2/2016
English
Current price: $10 Original price: $20 Discount: 50% off
2 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1 hour on-demand video
  • 1 min on-demand audio
  • 12 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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.
View Curriculum
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 is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
44 Lectures
01:20:13
+
Introduction - How the Query Optimizer Works
11 Lectures 16:11

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.

Preview 01:26

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.

Preview 00:59

Course Downloads
00:11

The SQL Server Engine has two major componets.

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

Preview 01:16

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.

Preview 01:30

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

Preview 01:44

How much does it cost to do all this?


Let's find out.

Preview 01:32

As a general rule we want plan cache reuse.

It's a good thing.

Let's talk about it in this lecture.

Plan Cache Reuse
01:20

Some times new plans will have to be created.

What are a few of the major causes of recompiles?

What Causes Recompilation?
00:51

Let's cover the new vernacular in video format.

Terminology
04:02

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

Summary
01:19

Quiz
10 questions
+
Execution Plans
16 Lectures 30:17

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.

Brain Overload Warning
00:41

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

Preview 02:34

Why are operators called iterators?

Let's find out why in this lecture.

The Iterator
00:30

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.

Execution Plan Output Options
01:37

The two most fundamental access methods in SQL Server.

Let's define them.

Scan VS Seek
01:56

The word scan in database parlance means bad.

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

Scanning Demos
02:09

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.

Seeking Demos
03:06

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

Let's define them and correct them.

The Bookmark Lookup
04:08

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

Let's cover them in this lecture.

Aggregations
02:02

What's a Hast Match operator?

In this lecture we will find out.

Hash Match Operator
03:05

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.

Joins - The Nested Loop
01:54

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.

Join - The Merge Join
01:25

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.

Joins - The Hash Join
01:17

Parallelism is a complicated topic.

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

Parallelism Introduction
01:13

There are lots of new terms in this lecture.

You many want to print them out.

Terminology
01:40

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

Summary
00:58

Quiz
10 questions
+
Cost Estimation
9 Lectures 17:13

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.

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

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

Quick Review
00:30

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.

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

Substantial data change operations (like insert, update, delete, or merge) change the data distribution in the table or indexed view.
Out of Date Statistics
01:17

Let's look at an execution plan and some stale statistics.

Stale Statistics Demo
01:16

Maintaining Statistics
01:25

Setting Up Statistics Maintenance Job
04:35

Terminology
01:16

Summary
00:54

Quiz
10 questions
+
Real World Examples: Fixing the Most Common Issues
7 Lectures 16:17

One of the most common operators you'll come across

You'll see it almost every day.

Let's learn what to look for and how to correct them.

The Scan On Large Tables
03:23

We use this in conjunction with execution plan analysis.

This gives us a more granular insight into how the pages move between the buffer pool and the disk.

Using SET STATISTICS IO ON
02:46

SET STATISTICS PROFILE ON
00:22

You can indeed add non-clustered indexes to heaps and make a dramatic improvement on your queries.

Let's look at how in this lecture.

Non-Clustered Indexes on Heaps
02:44

There's no such thing as a good sort.

Let's learn how to get rid of them in this lecture.

The No Good Sort Operator
03:29

Easy to spot, spools are a good place to improve query performance.

Unwinding The Spool
02:00

Summary
01:33

Quiz
10 questions
+
Conclusion
1 Lecture 00:23

Congratulations!!!!!

You've completed this course on execution plan analysis.

Congratulations and Thank You
00:23
About the Instructor
Mike West
4.2 Average rating
2,221 Reviews
35,838 Students
37 Courses
SQL Server and Machine Learning Evangelist

I've been a production SQL Server DBA most of my career.

I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.

Experience, education and passion

I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.

Certifications

Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).

Personal

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.