A SQL Server sort operator can replay its results, but only when the sort operates on at most one row. This article employs a sort to avoid a table spool in a query designed to find the closest match to a given value in a second table.
The rare circumstances when a SQL Server sort operator inside a nested loop can rewind (replay) cached results to avoid re-executing child plan operators.
Exploring a new query optimizer exploration rule in SQL Server 2019 can split large batch mode hash aggregates to avoid or reduce spilling to tempdb.
SQL Server has a bug with Halloween Protection when using an OUTPUT clause on a DELETE statement.
A MAXDOP n hint does not limit a SQL Server execution plan to using a maximum of n CPU cores concurrently 🤯
How and when the SQL Server query optimizer can pull a logical Group By operator above a logical Join.
New instant column data type changes possible in SQL Server 2016 when using data compression.
A few different ways to retrieve distinct values from a table, including some big "it just runs faster" wins in SQL Server 2019.
The SQL Server Eager Index Spool execution plan operator, and the optimizer rules capable of adding it into plans.
Performance spools and how they can be used by the optimizer to reduce the estimated cost of nested loops joins.
The internals of SQL Server batch mode data normalization and how it can affect execution plans and performance.
Demo scripts for SQL Server batch mode hash bitmap selectivity estimates. Simple and complex bitmap types. Compiled and compressed bitmaps.
A deep dive into SQL Server batch mode bitmaps revealing undocumented details that can greatly impact performance.
Two cases where the introduction of the internal ANY aggregate by the optimizer may cause wrong results.
Understanding the differences between SQL Server APPLY and nested loops joins, including details of query optimizer transformations.
Minimal logging for inserts into b-tree indexes (empty or not) using the Fast Load Context, and why it might not always work as expected.
The factors SQL Server considers when enabling minimal logging for inserts into an empty clustered table.
The factors SQL Server considers when enabling minimal logging for inserts into a table without a clustered index.