Page Free Space

Why Batch Mode Sort Spills Are So Slow Batch mode sorting was added to SQL Server in the 2016 release under compatib...

Fast Key Optimization for Row Mode Sorts SQL Server row-mode sorts generally use a custom implementation of the well-kn...

More Consistent Execution Plan Timings in SQL Server 2022 The updated showplan schema shipped with SSMS 19 preview 2 c...

SQL Server 2019 Aggregate Splitting The SQL Server 2019 query optimizer has a new trick available to improve the perfor...

A bug with Halloween Protection and the OUTPUT Clause Background The OUTPUT clause can be used to return results fro...

How MAXDOP Really Works A few days ago I ran a Twitter poll: The most popular answer gets highlighted by Twitter at ...

Nested Loops Prefetching Nested loops join query plans can be a lot more interesting (and complicated) than is commonly...

Two Partitioning Peculiarities Table partitioning in SQL Server is essentially a way of making multiple physical tables...

The Problem with Window Functions and Views Introduction Since their introduction in SQL Server 2005, window functions...

Execution Plan Analysis: The Mystery Work Table I love SQL Server execution plans. It is often easy to spot the cause o...

Halloween Protection – The Complete Series I have written a four-part series on the Halloween Problem . Some of you wi...

The Halloween Problem – Part 4 The Halloween Problem can have a number of important effects on execution plans. In this...

The Halloween Problem – Part 3 The MERGE statement (introduced in SQL Server 2008) allows us to perform a mixture of ...

The Halloween Problem – Part 2 In the first part of this series , we saw how the Halloween Problem applies to UPDATE q...

The Halloween Problem – Part 1 Much has been written over the years about understanding and optimizing SELECT queries,...

Optimizing T-SQL queries that change data Most tuning efforts for data-changing operations concentrate on the SELECT s...

MERGE Bug with Filtered Indexes A MERGE statement can fail, and incorrectly report a unique key violation when: The ...

Cardinality Estimation Bug with Lookups Estimated row counts on Key or RID Lookups where a filtering predicate is app...

Why Doesn’t Partition Elimination Work? Given a partitioned table and a simple SELECT query that compares the partitio...

Compute Scalars, Expressions and Execution Plan Performance The humble Compute Scalar is one of the least well-underst...