I was asked recently why Halloween Protection was needed for data modification statements that include a self-join of the target table. This gives me a chance to explain, while also covering some interesting product bug history from the SQL Server 7 and 2000 days.
The internal ANY aggregate may be considered by the query optimizer when particular T-SQL syntax is used. This article describes the transformation involved and the status of a bug in its implementation from SQL Server 2008 to 2022 inclusive.
Ever wondered what might go wrong if SQL Server allowed NOLOCK on the target of a data-changing operation? Find out courtesy of the weird locking behaviour of the TABLESAMPLE clause.
Updates to a nonclustered index are often performed as a delete followed by an insert. Most of the time, you won't notice the difference. There are edge cases where the internal operations can have a very dramatic effect on performance.
Mixing datetime with the newer date and time data types can result in unexpected query results. SQL Server 2016 intoduced a breaking change into the way the engine behaves to provide arguably more accurate results. The trouble is, you don't always want this kind of accuracy.
Deep internal technical details of how SQL Server builds an index using multiple cores. Simple offline index builds can use a more efficient plan involving a sort operator running in "multi sort" mode. Other parallel index builds, including online and resumable, use a strategy first released in SQL Server 2000.
An execution plan timing puzzle featuring a parallel Sort operator that runs for longer than the root Gather Streams. The explanation covers BPSORT waits and other execution plan internal details.
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.