Modern execution plans can contain a mixture of batch mode and row mode operators, with each mode exposing timings in a different way. SQL Server 2022 contains an improvement that is currently hidden behind a trace flag.
How the early SQL Server plan compilation process interacts with simple parameterization, including decoding and constant folding.
How the SQL Server parser interacts with simple parameterization.
The often contradictory information shown in execution plans about simple parameterization.
The quriky rules for data type assignment during simple parameterization.
Shell plans, plan stubs and how to cache only prepared plans (not ad-hoc).
The parameter embedding optimization enabled by a RECOMPILE query hint doesn't always work to your advanatge when large objects are involved.
How an adaptive join decides to use a batch mode hash join or row mode apply. Includes details of the threshold calculation.
Foreign key blocking under read committed snapshot isolation and related update conflicts under snapshot isolation.
Why you can sometimes see two sequential Parallelism operators in SQL Server execution plans.
How additional parallel tasks cooperate to complete plan execution, including returning rows to the client.
Exchange synchronization, CXPACKET waits, and parallel task opening.
Starting the query scan, "early phases" processing, and starting the first parallel tasks.
Iterative execution, invisible plan performance profilers, and how the top-level query scan begins.
The role of the parent task (coordinator) in preparing a plan for parallel execution. Includes initializing each operator and adding hidden profilers to collect runtime performance data such as row count and elapsed time.
An incorrect results bug with batch mode hash joins and a parallel eager table spool used for Halloween Protection. Unfixed before SQL Server 2022.
How SQL Server actual execution plan operator timings work, and why parallel row mode plan timings can be misleading.
Describes the structure of a sql_handle and explains how the SQL Server batch text hash component is calculated. Provides T-SQL functions to replicate the calculations.