Mastering the Basics of SQL Server Query Optimization

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.

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

Why are operators called iterators?

Let's find out why in this lecture.

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.

The two most fundamental access methods in SQL Server.

Let's define them.

The word scan in database parlance means bad.

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

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.

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

Let's define them and correct them.

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

Let's cover them in this lecture.

What's a Hast Match operator?

In this lecture we will find out.

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.

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.

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 is a complicated topic.

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

There are lots of new terms in this lecture.

You many want to print them out.

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

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.

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

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.

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

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.

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.

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.

There's no such thing as a good sort.

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

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

Congratulations!!!!!

You've completed this course on execution plan analysis.