The SQLServerFast Execution Plan Video Training
You know that performance of database queries is critical to the success of any application. Customers leave if the website feels slow. Reports that take too long to render are not used, however useful. So you want your queries to be fast and efficient, and to remain so as the size of the data grows.
But tuning those queries sometimes feels like fighting demons. The indexes you create are sometimes used, other times simply ignored. Changes that worked yesterday might not work tomorrow.
That’s where execution plans come in. Execution plans can reveal why your query is running slow, what the bottleneck is, and how you might fix that. But execution plans can be hard to read and understand. You might run into things that have no documentation, or documentation that is too hard to understand. There are some training resources but nothing is comprehensive and complete.
You need the SQLServerFast Execution Plan Video Training: an extensive set of videos to help SQL Server professionals how to read and understand execution plans, and how they can help understand and fix bad performance.
On this page:
Trailer
Structure
To help everyone get the most out of their time investment, the SQLServerFast Execution Plan Video Training is split into two levels: basic and advanced. The table below helps you understand which content is best for you.
| I am just starting my first SQL Server job. I’m still learning how to write T-SQL queries. I’ve heard about indexes but don’t really grasp the details yet. | You are not yet ready for this course.
Please come back when you feel more confident in T-SQL and have a basic understanding of indexes. |
| My queries don’t always run as fast as I would like. I have heard about execution plans but not used them yet. | The basic level of the course teaches you a lot. Start with the basic level of block 1, “Understanding Execution Plans”. After watching this, you are ready for the basic level of the other blocks. |
| I know a bit about execution plans. I have looked at them and I’ve learned to recognize some patterns and how to fix them. But I don’t really understand everything and I feel I often miss important details. | The basic level of the course will help you. You can view the blocks in any order.
The basic level of block 1 will have some information you already know, but also a lot of new information. I recommend investing the time to watch this too, even if some of the information is familiar. |
| I know a fair amount about execution plans. I regularly look at them, and if they are not too complex I understand most of what I see. But when they get more complex, or use uncommon operators, I start to struggle. | There is value for you in both the basic and the advanced level of the course.
The basic level will have a mix of new information and things you already know. The advanced level gives all the additional details you need. |
| I really know a lot about execution plans already, and I use them frequently, with a lot of success. But I still sometimes struggle to see the bigger picture, or to understand the fine details. | You can jump straight into the advanced levels. If you feel you need more information about specific areas, you might want to pick and choose selected chapters from the basic level to fill in the gaps. (See below for a detailed table of contents). |
| I am considered an expert on execution plans. I present on this subject to my co-workers and on local user groups. People learn a lot from me. But sometimes, I get questions that are beyond me. | You might not find enough value in watching the entire course, or even the advanced level only. But you can still choose to watch specific chapters. (See below for a detailed table of contents). |
Pricing and availability
I use Vimeo to host the videos of the SQLServerFast Execution Plan Video Training. This platform handles streaming, processes payments, and keeps track of who has purchased access to which videos.
Before your first purchase, you will need to create a Vimeo account (if you don’t have one yet). This can be a basic account, which is free (the paid plans are only for creators); and you can use it for all videos hosted on Vimeo, not only the SQLServerFast Execution Plan Video Training.
Please click the links below to find the pages for each block / level, where you can then purchase access to videos, or view videos you already purchased.
| Block and level | Duration | Price * |
| Block 1: Understanding Execution Plans – basic level | 7 chapters; play time 2:26:52 |
Free |
| Block 1: Understanding Execution Plans – advanced level | 5 chapters; play time 2:41:20 |
$24.95 per chapter; $99.95 for the whole level |
| Block 2: Reading data – basic level | 5 chapters; play time 2:46:32 |
$24.95 per chapter; $99.95 for the whole level |
| Block 2: Reading data – advanced level | 5 chapters; play time 2:56:27 |
$24.95 per chapter; $99.95 for the whole level |
| Block 3: Combining data – basic level | 6 chapters; play time 3:45:10 |
$24.95 per chapter; $119.95 for the whole level |
| Block 3: Combining data – advanced level | 4 chapters; play time 3:19:45 |
$24.95 / $34.95 per chapter; $99.95 for the whole level |
| Block 4: Sorting and grouping – basic level | 5 chapters | Not yet available ** |
| Block 4: Sorting and grouping – advanced level | 3 chapters | Not yet available ** |
| Block 5: Various data manipulations – basic level | 4 chapters | Not yet available ** |
| Block 5: Various data manipulations – advanced level | 2 chapters | Not yet available ** |
| Block 6: Technical operators – basic level | 5 chapters | Not yet available ** |
| Block 6: Technical operators – advanced level | 5 chapters | Not yet available ** |
| Block 7: Data modifications – basic level | 4 chapters | Not yet available ** |
| Block 7: Data modifications – advanced level | 4 chapters | Not yet available ** |
* Prices are set in dollars, but will be automatically converted to equivalent currency based on where you live. Additional taxes might apply.
** Unfortunately, I cannot share estimated dates for these blocks until approximately a month before release. I can only guarantee my high quality standards if I am not constrained by an already announced release date.
Detailed table of contents
I understand that your time is valuable. Before committing to watching many hours of video material, you want to know what to expect. The tables below outline, for each individual chapter, what to expect.
Block 1: Understanding Execution Plans, basic level
The seven chapters of this level cover execution plan basics: what is an execution plan, how does it help you, where to find it, and how to read it.
| Chapter | Duration | Contents | |
| Total | Demos | ||
| 1: What and why? | 09:58
|
None | How do declarative languages such as T-SQL differ from other languages? What are execution plans and why are they important? |
| 2: Requesting an execution plan | 16:00
|
03:43 | Three ways to request an execution plan for a query. How to use them? Which to use in what situation? |
| 3: How to read an execution plan | 18:56 | None | Operators (icons); data flows (arrows). Left-to-right or right-to-left? There are no bad operators! |
| 4: Properties | 27:11 | 13:54 | What are properties in an execution plan, where to find them, why are they relevant? Examples of a few important properties. |
| 5: Where to find execution plans | 32:32 | 13:13 | Various ways to find execution plans: Specific queries: SSMS options; SET statement. Finding problem plans on a busy server: plan cache, DMVs, Query Store, Extended Events. |
| 6: Cardinality in the execution plan | 22:47 | 06:08 | Estimated and actual number of rows; estimated and actual executions. How you can use these properties to find the root cause for some problems. Several situations where this information is given in confusing and misleading ways. |
| 7: Percentages in the execution plan | 19:28 | 11:20 | Query cost relative to batch. Operator cost relative to query. Actual number of rows relative to estimated number of rows. |
Block 1: Understanding Execution Plans, advanced level
In these videos, you will learn what many commonly used properties mean; how order of rows impacts execution plans; some internals of the optimization processes and how this affects the execution plan; and what batch mode is and how it affects performance.
| Chapter | Duration | Contents | |
| Total | Demos | ||
| 1: Properties of the plan | 29:31 | 03:16 | Properties on top left operator are actually about the plan as a whole. All relevant properties are explained here. |
| 2: Common operator properties | 39:09 | 23:40 | Properties that appear on many operators with the same meaning everywhere. Generic guidelines on how to read execution plans, trace data, and understand issues. |
| 3: Order of data in the data stream | 28:09 | 09:10 | When is order of data in the data stream relevant and when not? How do operators affect the order of data in the data stream? How do ordering considerations impact execution plans? |
| 4: Missing nodes | 24:14 | 11:51 | Phases in the optimization process. Effect of post optimization cleanup phase on Node ID property in execution plans. Confusing effect of post optimization cleanup on other properties. |
| 5: Batch mode versus row mode | 40:17 | 10:07 | What is row mode, what is batch mode, how do they differ? Internals of batch mode. Details of batch mode support in various versions of SQL Server. |
Block 2: Reading data, basic level
Most performance issues are caused by operators reading too much data, or reading data in an inefficient way. In this level, you will learn how most SQL Server data is organized on disk, and how scan, seek, and lookup operators traverse those storage structures to find the data you need, hopefully as efficient as possible. Understanding these basics will help you write more efficient queries and design more effective indexes.
| Chapter | Duration | Contents | |
| Total | Demos | ||
| 1: Storage structures | 27:49 | 02:28 | Storage structures used for “standard” (rowstore on-disk) data. Heaps, clustered index, nonclustered index. What are these structures, both conceptually and the internal details (B-tree, heap, IAM pages, index pages, etc) |
| 2: Scan operators | 40:59 | 09:42 | Operators covered: Table Scan, Index Scan, Clustered Index Scan for “standard” data. What happens “under the hood” in these operators? Which properties to look at? When is a scan better than a seek? What is predicate pushdown? |
| 3: Seek operators | 32:41 | 06:39 | Operators covered: Index Seek, Clustered Index Seek for “standard” data. What happens “under the hood” in these operators? Which properties to look at? Use case for seek operators. Considerations for indexing strategy. |
| 4: Lookup operators | 35:39 | 08:55 | Operators covered: Key Lookup, RID Lookup for “standard” data. What happens “under the hood” in these operators? Which properties to look at? Misleading row count estimate when predicate is pushed down. Avoiding lookups: considerations for clustered index choice; considerations for covering indexes. |
| 5: Special scans | 29:24 | 14:06 | Operators covered: Constant Scan, Deleted Scan, Inserted Scan. What happens “under the hood” in these operators? Which properties to look at? Constant Scan: How to understand its role when used in non-obvious ways. Deleted Scan, Inserted Scan: Where do these get their data from? |
Block 2: Reading data, advanced level
Most performance issues are caused by operators reading too much data, or reading data in an inefficient way. In this level, we build on the knowledge from the basic level to show how SQL Server deals with columnstore and memory-optimized data. We explain the internals of several specialized index types. We also cover reading data in parallelism or batch mode, plus some advanced optimizations. All of this will ensure that you can understanding everything the execution plans tells you about how data was accessed.
| Chapter | Duration | Contents | |
| Total | Demos | ||
| 1: Columnstore indexes | 38:02 | 07:26 | Operators covered: Columnstore Index Scan, Key Lookup (for columnstore index). Row-based vs column-based storage. Storage structures of columnstore indexes. Rowgroup, deltastore, deleted bitmap. Segment elimination. |
| 2: Memory-optimized indexes | 49:43 | 12:25 | Operators covered: Table Scan, Index Scan, Index Seek (for memory-optimized data). Storage structures of memory-optimized data. Varheap, Hash index, Bw-tree index. In-memory columnstore indexes. Natively compiled stored procedures. |
| 3: Special index types | 24:50 | 09:50 | Filtered indexes: effect on storage structure. XML indexes: internal structure and read strategies. Spatial indexes: tessellation, internal structure, read strategies. Full-text indexes: usage in execution plans. |
| 4: Reading data in parallel or batch mode | 23:09 | 09:32 | Scans and ranges seeks in a parallel execution plan.
Parallel Page Supplier. |
| 5: Assorted read optimizations | 40:43 | 21:33 | Operators covered: Merge Interval. Read-ahead reading. Advanced scan aka merry-go-round scan. Dynamic seek range. Reading from partitioned tables and indexes. |
Block 3: Combining data, basic level
When a query combines data from multiple sources, then there will be operators in the execution plan to combine the relevant data. Each operator has its own benefits, its own limitations, and its own performance characteristics. This level teaches you the basic algorithm of each, which will help you understand how they impact your query performance.
| Chapter | Duration | Contents | |
| Total | Demos | ||
| 1: Logical join types | 31:59 | 06:20 | Logical join types: Inner Join, Outer Join, Semi Join, Anti Semi Join, probed join. Other ways to combine data: Concatenation, Union, Sequence, Switch.| Unexpected join type transformations. |
| 2: Nested Loops | 40:33 | 14:39 | Operators covered: Nested Loops. Performance characteristics and risks of Nested Loops. Detailed visualization of the Nested Loops algorithm. Rebinds and rewinds. Properties of the Nested Loops operator. |
| 3: Merge Join | 37:57 | 17:03 | Operators covered: Merge Join. Performance characteristics of Merge Join. The cost of sorting data, and how the optimizer gets around it. Detailed visualization of the Merge Join algorithm. Properties of the Merge Join operator. |
| 4: Hash Match | 46:23 | 13:01 | Operators covered: Hash Match. Performance characteristics and risks of Hash Match. Hash functions and hash tables. Memory Grant for a Hash Match. Detailed visualization of the Hash Match algorithm. Properties of the Hash Match operator. |
| 5: Adaptive Join | 33:20 | 06:18 | Operators covered: Adaptive Join. Performance comparison of Nested Loops, Hash Match, and Adaptive Join. How and when Adaptive Join necessitates conversions between row mode and batch mode. The algorithm for Adaptive Join. Properties of the Adaptive Join operator. |
| 6: Other combining operators | 34:58 | 18:05 | Operators covered: Concatenation, Sequence, Switch. The algorithms for Concatenation, Sequence, and Switch. Similarities and differences between these three operators. Relevant properties for each of these operators. Interleaved execution. |
Block 3: Combining data, advanced level
In this level, you will learn how relatively simple modifications to the logic of each join operator allow it to perform various logical join types. We also look at various advanced topics, such as optimizations for Nested Loops, many to many Merge Join, spilling of a Hash Match, and practical use cases for the Adaptive Join.
| Chapter | Duration | Contents | |
| Total | Demos | ||
| 1: Nested Loops (advanced) | 49:38 | 24:49 | Operators covered: Nested Loops. Modifications to Nested Loops algorithm for all supported join types. Workarounds for unsupported join types. Prefetching. Optimized Nested Loops. |
| 2: Merge Join (advanced) | 59:35 | 21:25 | Operators covered: Merge Join. Modifications to Merge Join algorithm for all supported join types. Extra overhead for a many to many Merge Join. Special use cases for Merge Join: Ordered concatenation and cartesian product. |
| 3: Hash Match (advanced) | 54:30 | 16:18 | Operators covered: Hash Match. Modifications to Hash Join algorithm for all supported join types. What happens during a hash spill? Memory sharing in larger execution plans. Hash teams. |
| 4: Adaptive Join (advanced) | 36:02 | 07:52 | Operators covered: Adaptive Join. Modifications to Adaptive Join algorithms for all supported join types. Optimizations for (modified) Nested Loops algorithm. Hash spills for an Adaptive Join. Role of Adaptive Join in the execution plan. |