Skip to content Skip to sidebar Skip to footer

Understanding Execution Plans and Indexes in SQL Server

Understanding Execution Plans and Indexes in SQL Server

An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to calculate the most ...

Enroll Now

What you'll learn

  1. Participants will get deeper knowledge on Clustered ,Non Clustered Indexes & Fragmentation Concepts
  2. Participants will learn commonly used Execution Plans
  3. Participants will learn to find the patterns which may be slowing down the query performance
  4. 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 topics

Index Concepts in SQL Server

  • Page in SQL Server
  • Extent in SQL Server
  • Heap in SQL Server
  • Clustered Index
  • Non Clustered Index
  • Heap with Non Clustered Index
  • Indexes are not very Deep
  • Composite Index
  • Index Fragmentation
  • Index Creation using T-SQL Commands
  • Index Rebuild and ReOrganize
  • FillFactor
  • Included Columns
  • Data Type on Clustered Index

Execution Plan

  • Statistics
  • Actual Execution Plan
  • Estimated Execution Plan

Common Operators

  • Table Scan
  • Index Scan
  • Index Seek
  • Key Lookup
  • RowID Lookup
  • Nested Loop
  • Sort
  • Merge Join
  • Hash Join
  • Compute Scalar
  • Stream Aggregate
  • Hash Aggregate
  • Left Semi Join
  • Right Semi Join
  • Left Anti Semi Join
  • Right Anti Semi Join
  • Segment
  • Sequence Project
  • Lazy Spool
  • Eager Spool

Parallelism - Distribute Stream ,Gather Stream & Repartition Stream

  • More on Execution Plans
  • Halloween Problem
  • Execution Plan of Insert Operation
  • Execution Plan of Delete Operation
  • Execution Plan of Update Operation
  • Subtree cost related to Parallelism
  • Subtree Cost of Stream and Hash Aggregate

Index supports Wild Card

  1. Patterns to Note to Tune Queries
  2. Parameter Sniffing
  3. Parameter Sensitive Plan Optimization in Sql Server 2022
  4. MergeJoin Patterns
  5. Hash Join Patterns
  6. Key Lookup Patterns
  7. Sort Indicators
  8. Aggregate Indicators
  9. Parallelism Indicators
  10. Warnings in Execution Plan
From this course you will learn how to read and understand execution plans which will help in tuning Transact SQL Commands

Who this course is for:

  • 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.

Courses to get you started -- > Understanding Execution Plans and Indexes in SQL Server

Online Course CoupoNED based Analytics Education Company and aims at Bringing Together the analytics companies and interested Learners.