Page Free Space

StarJoinInfo in SQL Server Execution Plans

Details and demos for the StarJoinInfo structures sometimes seen in SQL Server execution plans associated with an automatic performance optimization.

Cardinality Estimation for Multiple Predicates

How selectivity is computed for multiple filters in the original and default SQL Server cardinality estimation models.

Statistics on Indexed Views

Multiple reasons to prefer using the NOEXPAND hint on indexed views, even on Enterprise Edition.

Parallel Execution Plans—Branches and Threads

How SQL Server row mode parallel execution plans work.

Nested Loops Prefetching

SQL Server nested loops join internals including ordered and unordered delayed prefetching and undocumented trace flags.

Parameter Sniffing, Embedding, and the RECOMPILE Options

Internal details of SQL Server parameter sniffing, embedding (PEO), and parameterization.

Incorrect Results Caused By Adding an Index

A SQL Server incorrect results bug with merge join, partitioning, and a descending index.

Incorrect Results with Merge Join

A detailed analysis of a SQL Server optimizer bug that causes incorrect results when a descending non-unique index exists on a partitioned table.

Two Partitioning Peculiarities

Join and group by clauses may need to be written in a specific way to get the best results from the SQL Server query optimizer when using partitioning.

Aggregates and Partitioning

Improving performance for SQL Server MIN and MAX queries on partitioned tables and working around query optimizer limitations.

Working Around Missed Optimizations

SQL Server cardinality estimation bugs with partial aggregates and union all concatenation. Merge and concat union details.

Optimization Phases and Missed Opportunities

SQL Server query optimizer search phases and aggregate transformations using TOP.

Improving Partitioned Table Join Performance

Improving the performance of SQL Server partitioned table joins using a manual query rewrite to implement a collocated (per-partition) join.

Hello Operator, My Switch Is Bored

The SQL Server "Switch" execution plan operator, cardinality estimates on filtered indexes, and invisible operators.

Optimizer Limitations with Filtered Indexes

SQL Server filtered unique indexes do not provide uniqueness information to the query optimizer. Redundant predicates may be needed to match filtered indexes.

The Problem with Window Functions and Views

The SQL Server query optimizer can fail to push a filter past a windowing function. This article shows how to improve the performance of queries that filter on views with window functions like ROW_NUMBER and RANK.

Execution Plan Analysis: The Mystery Work Table

Internal details about execution plans and work tables. Nested loops prefetching and manufactured LOBs causing poor performance, and how to fix it.

Halloween Protection The Complete Series

Halloween Protection in SQL Server and how it affects INSERT, UPDATE, DELETE, and MERGE statements. Details of internal optimizations.