What you'll learn:
- Understand how SQL Server stores and accesses data using pages, extents, heaps, and indexes
- Design and maintain effective indexing strategies using clustered and nonclustered indexes
- Identify performance issues using execution plan operators, warnings, and indicators
- Tune queries by recognizing common execution plan patterns and anti-patterns
- Understand transactions and concurrency behavior in SQL Server
- Identify and resolve concurrency problems such as dirty reads, phantom reads, and deadlocks
- Choose the correct transaction isolation level based on real-world scenarios
- Understand and implement In-Memory OLTP, including tables, indexes, and native compiled procedures
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
Patterns to Note to Tune Queries
Parameter Sniffing
Parameter Sensitive Plan Optimization in Sql Server 2022
MergeJoin Patterns
Hash Join Patterns
Key Lookup Patterns
Sort Indicators
Aggregate Indicators
Parallelism Indicators
Warnings in Execution Plan
From these topocs you will learn how to read and understand execution plans which will help in tuning Transact SQL Commands
Later sections of the online video training course include topics related to Transactions , Concurrency Problems , their solutions using Transaction Isolation Levels . Another major portion of the course consists of In-Memory OLTP in T-SQL .
The details topics are
Transactions
Auto Commit Transactions
Implicit Transactions
Explicit Transactions
Deadlock
Lock Modes
Concurrency Problems
Dirty Read Problem
Non Repeatable Read Problem
Phantom Read Problem
Lost Update Problem
Isolation Levels and Solutions
Read Uncommitted Isolation Level
Read Committed Isolation Level
Repeatable Read Isolation Level
Serializable Isolation Level
Scenario where Repeatable Read or Serializable or Snapshot can be used
Snapshot Isolation Level
Read Committed Snapshot Isolation Level
In-Memory OLTP
What are In-memory Tables
Scenarios of In-memory Table
Creating In-memory Table and Testing its performance
In-memory Table Indexes
Native Compiled Stored Procedure
Migration from Existing Disk Based Tables to In-memory Table
In-Memory OLTP Isolation Levels
In-Memory OLTP Snapshot Isolation Level
In-Memory OLTP Repeatable Read Isolation Level
In-Memory OLTP Serializable Isolation Level
