Understanding Execution Plans And Indexes In Sql Server

Learn Step By Step Explanation of Indexes and Execution Plans using T-SQL Commands in Microsoft SQL Server

What you'll learn

Participants will get deeper knowledge on Clustered ,Non Clustered Indexes & Fragmentation Concepts

Participants will learn commonly used Execution Plans

Participants will learn to find the patterns which may be slowing down the query performance

This course will Help Participant to tune already written query or write T-SQL in a better way

Requirements

RDBMS concepts should be known & should have good hands-on on T-SQL Commands

SQL Server 2022 (lower version can work also) and SQL Server Management Studio should be installed already

Description

This course is not for beginners. One should have prior knowledge on T-SQL commands before enrolling into this course. The course contains the following topicsIndex Concepts in SQL ServerPage in SQL ServerExtent in SQL ServerHeap in SQL ServerClustered IndexNon Clustered IndexHeap with Non Clustered IndexIndexes are not very DeepComposite IndexIndex FragmentationIndex Creation using T-SQL CommandsIndex Rebuild and ReOrganizeFillFactorIncluded ColumnsData Type on Clustered IndexExecution PlanStatisticsActual Execution PlanEstimated Execution PlanCommon OperatorsTable Scan Index Scan Index Seek Key LookupRowID Lookup Nested Loop SortMerge JoinHash JoinCompute ScalarStream AggregateHash AggregateLeft Semi JoinRight Semi JoinLeft Anti Semi JoinRight Anti Semi JoinSegment Sequence ProjectLazy SpoolEager SpoolParallelism - Distribute Stream ,Gather Stream & Repartition StreamMore on Execution PlansHalloween ProblemExecution Plan of Insert OperationExecution Plan of Delete OperationExecution Plan of Update OperationPassthrough property in Nested LoopSubtree Cost of Stream and Hash AggregateIndex supports Wild CardPatterns to Note to Tune QueriesParameter Sniffing Parameter Sensitive Plan Optimization in Sql Server 2022MergeJoin PatternsHash Join PatternsKey Lookup PatternsSort Patterns Aggregate Patterns Parallelism PatternsWarnings in Execution Plan

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Page in SQL Server

Lecture 3 Extent in SQL Server

Lecture 4 Heap in SQL Server

Lecture 5 Clustered Index

Lecture 6 Non Clustered Index

Lecture 7 Heap with Non Clustered Index

Lecture 8 Indexes are not very Deep

Lecture 9 Composite Index Part 1

Lecture 10 Composite Index Part 2

Lecture 11 Index Fragmentation

Lecture 12 More on Fragmentation

Lecture 13 Index Creation using T-SQL Commands

Lecture 14 Index Rebuild and ReOrganize

Lecture 15 Index Fragmentation Practical

Lecture 16 FillFactor

Lecture 17 T-SQL Command to introduce FillFactor in an Index

Lecture 18 Included Columns

Lecture 19 Data Type on Clustered Index

Section 2: Execution Plans

Lecture 20 What are Estimated & Actual Execution Plans along with Statistics

Lecture 21 Download and Restore AdventureWorks Database

Lecture 22 Table Scan Operator

Lecture 23 Index Scan Operator

Lecture 24 Index Seek Operator

Lecture 25 Key Lookup, RowID Lookup & Nested Loop Operators

Lecture 26 Index supports Wild Card

Lecture 27 Sort in Execution Plan

Lecture 28 Merge Join Operator

Lecture 29 Hash Join,Merge Join & Nested Loop Operators

Lecture 30 Compute Scalar Operator

Lecture 31 Stream and Hash Aggregate Operators

Lecture 32 Subtree Cost of Stream and Hash Aggregate

Lecture 33 Semi Joins & Anti Semi Joins in Execution Plan

Lecture 34 Merge using Left Anti Semi Join in Execution Plan

Lecture 35 Execution Plan of Insert Operation

Lecture 36 Passthrough property in Nested Loop

Lecture 37 Execution Plan of Delete Operation

Lecture 38 Parallelism in Execution Plan

Lecture 39 Distribute Stream ,Gather Stream & Repartition Stream

Lecture 40 Segment Operator & Sequence Project Operators in Execution Plan

Lecture 41 Lazy Spool in Execution Plan

Lecture 42 Eager Spool and Halloween Problem Practical

Section 3: Patterns to Notice in Execution Plans

Lecture 43 Parameter Sniffing vs Parameter Sensitive Plan Optimization in Sql Server 2022

Lecture 44 Parameter Sniffing vs Parameter Sensitive Plan Optimization Practical

Lecture 45 Merge Join Patterns

Lecture 46 Hash Join Patterns

Lecture 47 Key Lookup Patterns

Lecture 48 Sort Aggregate & Parallelism Patterns

Lecture 49 Warnings in Execution Plan

The partitcipants who have prior knowledge of T-SQL commands.,This course is for SQL Server Developers , SQL Server DBAs, .NET Developers who writes T-SQL Commands , Data Analysts etc.