T-SQL Execution Plans, Indexes, Transactions, In-Memory OLTP

Compare estimated and actual execution plans in SQL Server, using statistics to predict query behavior and diagnose discrepancies caused by outdated statistics or resource limits.

Learn to download AdventureWorks backups for SQL Server versions from Microsoft Learn, restore the database locally, and run queries to view execution plans.

Explains table scan on a heap when no clustered index exists, forcing a full table scan despite where clauses, unless a non-clustered index covers a column like database log ID.

Examine the index scan operator, showing how clustered and non-clustered indexes influence scans in the execution plan using the AdventureWorks production.product table.

Explore how an index seek uses a clustered index on the id column to locate a single row in the product table, highlighting a seek predicate and leaf-level retrieval.

Explore key lookups, row ID lookups, and nested loop operators in SQL Server execution plans, using non-clustered, clustered indexes, and heaps.

Explore how wild cards are supported by indexes and how SQL Server chooses seek or scan, using key lookups, nested loops, and full index scans.

Explore how the sort operator orders rows and how execution plans may show sorting or rely on existing indexes, with examples of clustered and non-clustered indexes, scans, and lookups.

Learn how the merge join matches rows from two sorted tables on the ID in SQL Server, and why unsorted foreign keys may lead to sorting or a hash join.

Explain how hash match, merge join, and nested loop operators process joins in SQL Server, detailing build and probe phases, index choices, key lookups, and execution plans.

Examine the compute scalar operator in the execution plan that derives a full name from first and last names, and see how a non-clustered index avoids sorting to speed queries.

Compare stream and hash aggregate operators in SQL Server, showing how sorted data enables stream aggregation and unsorted data triggers hash aggregation for group by and sums.

Adding an index switches the plan from a hash match to a stream aggregate, lowering the estimated subtree cost from 0.27 to about 0.062 and improving efficiency.

Explore semi joins and anti semi joins in execution plans, including left and right variants, demonstrated inside a nested loop with practical examples.

Explore left anti semi join in execution plans using AdventureWorks 2022, highlighting clustered and non-clustered indexes and a merge join with stream aggregates for nonmatching records.

Understand how insert operations build execution plans with constant scan, compute scalar for identity values, and asserts that enforce referential integrity, foreign keys, and check constraints.

See how a delete operation checks foreign key references between geography and employee tables, blocking deletion if the geography id exists, and an estimated execution plan shows cluster index delete.

Explore how parallelism uses multiple CPUs to execute a single sql statement, identify parallel plans in execution plans, and understand the cost threshold of parallelism.

Explore distribute stream, gather stream, and repartition stream in SQL Server execution plans, showing how data is repartitioned across CPUs and then gathered into a single stream.

Describe how the segment operator groups input data for window functions and how the sequence project adds a ranking column to compute on an ordered set in an execution plan.

Examine lazy spool versus eager spool in SQL Server's execution plan, where a tempdb temporary table is loaded on demand and reused across segments to filter by average line total.

Understand how eager spool loads all data into tempdb to prevent the Halloween problem, using a staging temp table to update safely instead of reading from the index.

Analyze an execution plan by recognizing patterns and indicators to tune queries, and understand the difference between parameter sniffing and parameter sensitive plan optimization.

Compare parameter sniffing to parameter sensitive plan optimization in SQL Server 2022. Multiple execution plans can coexist in cache to handle uneven data distributions.

Explore parameter sniffing and parameter sensitive plan optimization (SPO) in SQL Server with a practical example, showing how first parameter values trigger scans while others prompt seeks.

Study merge join patterns by noting outdated statistics cause underestimation and tempdb spills during sorting, and assess how index choices and estimated versus actual row counts shape execution plans.

Explore hash join patterns in SQL Server, including build and probe phases, outer vs inner tables, cardinality issues, and memory spills to tempdb in hash match executions.

Explore key lookup patterns in SQL Server execution plans, comparing non-clustered index seeks, composite indexes, and included columns, and weigh performance against insert, update, and cardinality considerations.

Analyze costly sorts and aggregates in SQL Server execution plans, and use indexing to remove sorts, convert hash to stream aggregates, and adjust parallelism for data warehousing vs OLTP.

Explore execution plan warnings, including tempdb spill and columns with no statistics, and learn how automatic statistics, or its settings, impact indexing and query scans when date conversions occur.

Learn index concepts and commonly used execution plan operators, identify indicators and patterns in execution plans, and use these concepts to tune SQL commands.

Define what a transaction is in T-SQL, showing that all commands inside a transaction execute together or roll back as a single unit, with an example of atomicity.

Implement and demonstrate a transactional sequence for orders and order_details by using begin transaction, commit, and rollback inside a try-catch block to ensure all-or-nothing data integrity.

Explain the dirty read problem in SQL Server, where uncommitted data can be read via no log hints, causing inconsistent results during concurrent transactions.

Explore the lost update problem in SQL Server, where concurrent salary updates are overwritten by locking and waiting. See an SSMS demonstration of begin transaction, update, and commit order.

Explore the read uncommitted isolation level and how it bypasses concurrency protections, enabling dirty reads and showcasing locking behavior and rollback impact with a SQL Server example.

Explore the read committed isolation level, SQL Server's default, which reads only committed data and avoids uncommitted values, waiting for commit or rollback to reveal the final result.

Explain repeatable read isolation level, a stricter alternative to read committed that prevents non-repeatable and dirty reads by locking data during a transaction, and note phantom reads require serializable.

Demonstrate how serializable isolation prevents non-repeatable and phantom reads by locking key ranges, causing inserts to wait until commit, and emphasize indexing on date of birth to avoid table locks.

Explore a real-time scenario where reading the sales table twice within a transaction can yield mismatched details and summary, and learn how repeatable read, serializable, or snapshot isolation prevent this.

Explain how snapshot isolation level prevents lost updates by creating per-transaction versions in TempDB, allowing concurrent updates to proceed or fail with update conflicts that require retries.

Explore snapshot isolation as an alternative to serializable and repeatable read, showing how reads create versions in tempdb without locks and how commits remove versions, with caveats about tempdb growth.

Enable read committed snapshot at the database level, observe how transactions read the last committed value without waiting, and compare to read committed behavior, including tempdb impact.

Explore auto-commit, explicit, and implicit transactions in SQL Server, showing how single statements are transactions, how begin, commit, and rollback manage, and how implicit transactions work at the session level.

Analyze how deadlocks occur when two transactions wait on locked records, how SQL Server detects them, rolls back a victim (error 1205), and how timing adjustments prevent recurrence.

Explore how SQL Server uses lock modes such as shared, exclusive, update, and intent, along with schema locks and page-level behavior to maintain data consistency.

Compare pessimistic and optimistic concurrency control in SQL Server, detailing how locks occur during update in pessimistic mode and how versioning happens during reads in optimistic mode, including snapshot isolation.

Discover in-memory OLTP: memory-resident tables with no locking, using multi-version concurrency and in-RAM versions to support fast, concurrent updates without burdening tempdb.

Explore when in-memory oltp tables excel in high-concurrency workloads by avoiding heavy locking and hot pages, and apply them to session state, data staging, and etl workflows.

Create and test memory-optimized tables in SQL Server, choose durability options (schema and data vs schema only), configure memory-optimized file groups, and compare performance against disk-based tables.

Explore hash indexes on in-memory tables, noting bucket counts for point lookups and inserts and their limits on inequalities and sorting, then compare to range non-clustered indexes.

Explore native compiled stored procedures that access memory-optimized tables, translating into C and machine code for fast execution. Learn about schema binding and execute as owner to maximize performance.

Migrate disk-based tables to in-memory tables using the memory optimization advisor, review restrictions like foreign keys and data types, and copy data with careful backup and renaming steps.

Learn how in-memory OLTP snapshot isolation keeps reads stable during a transaction without locking, prevents lost updates, and uses snapshot elevation or hints for memory-optimized tables.

Understand repeatable read isolation in in-memory OLTP, where reads within a transaction yield the same results and include snapshot features. Explore rollback on commit and phantom reads with SSMS demonstrations.

Explains the in-memory OLTP serializable isolation level, combining snapshot and repeatable read to ensure consistent reads and detect phantom or modified data at commit.