SQL Server 2019 Aggregate Splitting
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.
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.
ANY
Aggregate Transformations are BrokenTwo 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.
INSERT…SELECT
and Fast Load ContextMinimal logging for inserts into b-tree indexes (empty or not) using the Fast Load Context, and why it might not always work as expected.
INSERT…SELECT
into Empty Clustered TablesThe factors SQL Server considers when enabling minimal logging for inserts into an empty clustered table.
INSERT…SELECT
into Heap TablesThe factors SQL Server considers when enabling minimal logging for inserts into a table without a clustered index.
A deep dive into columnstore grouped aggregation pushdown, showing where this optimization is unsuccessful or not attempted, using the undocumented SQL Server command DBCC CSINDEX
.
IGNORE_DUP_KEY
is slower on clustered indexesWhy the IGNORE_DUP_KEY
index option is much slower on a clustered index than a nonclustered unique index. Detail execution plan analysis and internal engine details.