The Lock Escalation Threshold—Part 1
How and when lock escalation is triggered in SQL Server, clarifying misleading information you may have seen in official documentation and other sources.
Articles
Page 2 of 11
— showing articles
19 to
36
of 182
Reducing NESTING TRANSACTION FULL Latch Contention
How to reduce contention on the NESTING TRANSACTION FULL latch in SQL Server 2019 using an undocumented trace flag.
Simple Parameterization and Trivial Plans—Part 6
Simplification, trivial plans, parameter safety, and parallelism. Includes a flowchart of the whole simple parameterization process.
More Consistent Execution Plan Timings in SQL Server 2022
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.
Simple Parameterization and Trivial Plans—Part 5
How the early SQL Server plan compilation process interacts with simple parameterization, including decoding and constant folding.
Simple Parameterization and Trivial Plans—Part 4
How the SQL Server parser interacts with simple parameterization.
Simple Parameterization and Trivial Plans—Part 3
The often contradictory information shown in execution plans about simple parameterization.
Simple Parameterization and Trivial Plans—Part 2
The quriky rules for data type assignment during simple parameterization.
Simple Parameterization and Trivial Plans—Part 1
Shell plans, plan stubs and how to cache only prepared plans (not ad-hoc).
Be Careful with LOBs and OPTION (RECOMPILE)
The parameter embedding optimization enabled by a RECOMPILE query hint doesn't always work to your advanatge when large objects are involved.
The Adaptive Join Threshold
How an adaptive join decides to use a batch mode hash join or row mode apply. Includes details of the threshold calculation.
Foreign Keys, Blocking, and Update Conflicts
Foreign key blocking under read committed snapshot isolation and related update conflicts under snapshot isolation.
Empty Parallel Zones in SQL Server Execution Plans
Why you can sometimes see two sequential Parallelism operators in SQL Server execution plans.
How Parallel Plans Start Up—Part 5
How additional parallel tasks cooperate to complete plan execution, including returning rows to the client.
How Parallel Plans Start Up—Part 4
Exchange synchronization, CXPACKET waits, and parallel task opening.
How Parallel Plans Start Up—Part 3
Starting the query scan, "early phases" processing, and starting the first parallel tasks.
How Parallel Plans Start Up—Part 2
Iterative execution, invisible plan performance profilers, and how the top-level query scan begins.
How Parallel Plans Start Up—Part 1
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.