Nested Loops Joins and Performance Spools
Performance spools and how they can be used by the optimizer to reduce the estimated cost of nested loops joins.
Articles
Page 4 of 11
— showing articles
55 to
72
of 188
Batch Mode Normalization and Performance
The internals of SQL Server batch mode data normalization and how it can affect execution plans and performance.
Batch Mode Bitmap Demos
Demo scripts for SQL Server batch mode hash bitmap selectivity estimates. Simple and complex bitmap types. Compiled and compressed bitmaps.
Batch Mode Bitmaps in SQL Server
A deep dive into SQL Server batch mode bitmaps revealing undocumented details that can greatly impact performance.
Some ANY Aggregate Transformations are Broken
Two cases where the introduction of the internal ANY aggregate by the optimizer may cause wrong results.
Apply versus Nested Loops Join
Understanding the differences between SQL Server APPLY and nested loops joins, including details of query optimizer transformations.
Minimal Logging with INSERT…SELECT and Fast Load Context
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.
Minimal Logging with INSERT…SELECT into Empty Clustered Tables
The factors SQL Server considers when enabling minimal logging for inserts into an empty clustered table.
Minimal Logging with INSERT…SELECT into Heap Tables
The factors SQL Server considers when enabling minimal logging for inserts into a table without a clustered index.
Columnstore Grouped Aggregate Pushdown
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.
Why IGNORE_DUP_KEY is slower on clustered indexes
Why 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.
Join Cardinality Estimation using Histogram Coarse Alignment
How the new SQL Server cardinality estimator uses histogram coarse alignment.
Row Goals 4: An Anti Join Anti Pattern
A redundant and problematic TOP (1) added to the inner side of an apply anti join.
Row Goals 3: Anti Semi Joins
An anti join does not automatically come with a 'row goal'. This article describes anti joins and the conditions required for the optimizer to add a row goal.
Row Goals 2: Semi Joins
When the SQL Server query optimizer adds a row goal for a semi join. How you can spot the effects in showplan and trace flag output.
Row Goals 1: Setting and Identifying Row Goals
Information about SQL Server row goals and why they're important when trying to understand why the query optimizer made a particular choice.
Trigram Wildcard String Search in SQL Server
Using character substrings (trigrams) to speed up wildcard searches in SQL Server. Provides an fairly complete example implementation.
Cardinality Estimation: Combining Density Statistics
Using a debugger to explore how SQL Server derives estimates when combining multiple single-column statistics.