Wouldn't it be nice not to have to worry about the virtual log file sizes SQL Server creates? There's an undocumented procedure in SQL Server 2022 to achieve this. Though currently unsupported, maybe this facility will become available in a future version.
A deep dive into why SQL Server batch mode sorts can be so slow when they spill—much slower than the equivalent row mode sort. Includes many other internal details of row and batch mode sorting.
Details of an internal optimization SQL Server uses to speed up row mode sorts.
The optimization is only available for a small number of sort key data types.
An efficient technique to batch bulk load structured data from a file into multiple tables on the server. Allows for server-side identity columns and foreign key relationships involving those identity values.
More on the deep internals of SQL Server lock escalation, including the use of Lock Classes and lock lifetime extension.
How parallelism affects lock escalation.
How and when lock escalation is triggered in SQL Server, clarifying misleading information you may have seen in official documentation and other sources.
How to reduce contention on the NESTING TRANSACTION FULL latch in SQL Server 2019 using an undocumented trace flag.
Simplification, trivial plans, parameter safety, and parallelism. Includes a flowchart of the whole simple parameterization process.
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.