Page Free Space

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.

The Halloween Problem—Part 4

SQL Server Halloween Protection and the query optimizer. Redundant sorts and other non-eager table spool options.

The Halloween Problem—Part 3

SQL Server Halloween Protection special optimizations for the MERGE statement. Hole-filling and rowset sharing.

The Halloween Problem—Part 2

When SQL Server Halloween Protection is needed for INSERT and DELETE statements. Constraint checking and phase separation.

The Halloween Problem—Part 1

Halloween Protection for SQL Server UPDATE queries. Why protection is needed and how it is implemented.

Incorrect Results with Merge and Indexed Views

Incorrect results from a SQL Server query using MERGE, indexed views and foreign keys.

A creative use of IGNORE_DUP_KEY

Adding the IGNORE_DUP_KEY option to an existing primary key without downtime. How the execution plans generated for IGNORE_DUP_KEY work.

Optimizing T-SQL queries that change data

Optimizing the performance of SQL Server INSERT, UPDATE, DELETE, and MERGE statements. Execution plan analysis and undocumented trace flags. Narrow (per-row) and wide (per-index) plans.

MERGE Bug with Filtered Indexes

A SQL Server bug with unique filtered indexes. Covers narrow update plans and the Split, Sort, Collapse operator combination.

Cardinality Estimation Bug with Lookups

Incorrect SQL Server estimated row counts on Key and RID Lookup operators where a Filter is applied.

Why Doesn’t Partition Elimination Work?

SQL Server can fail to eliminate partitions from a query due to implicit type conversion.

Compute Scalars, Expressions and Execution Plan Performance

How the SQL Server Compute Scalar operator works internally.