Page Free Space

sql_handle and the SQL Server batch text hash This article describes the structure of a sql_handle and shows how the...

Closest Match with Sort Rewinds In When Do SQL Server Sorts Rewind? I described how most sorts can only rewind when th...

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 ...

Pulling Group By Above a Join One of the transformations available to the SQL Server query optimizer is pulling a logic...

Batch Mode Bitmap Demos This is a companion post to my main article Batch Mode Bitmaps in SQL Server . This post provid...

Apply versus Nested Loops Join SQL is a declarative language. We use SQL to write a logical query specification that d...

SQL Server Temporary Object Caching Creating a table is a relatively resource-intensive and time-consuming operation. T...

Allocation Order Scans When an execution plan includes a scan of a b-tree index structure, the storage engine may be ab...

Cardinality Estimation for Disjunctive Predicates in SQL Server 2014 Introduction Back in January 2014, I wrote an art...

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

Parameter Sniffing, Embedding, and the RECOMPILE Options Parameter Sniffing Query parameterization promotes the reuse ...

Incorrect Results Caused By Adding an Index Say you have the following two tables, one partitioned and one not: CREATE...

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

Aggregates and Partitioning The changes in the internal representation of partitioned tables between SQL Server 2005 an...

Working Around Missed Optimizations In my last post , we saw how a query featuring a scalar aggregate could be transfor...

Optimization Phases and Missed Opportunities There are two complementary skills that are very useful in query tuning. O...

Improving Partitioned Table Join Performance The query optimizer does not always choose an optimal strategy when joinin...

Hello Operator, My Switch Is Bored This post is in two parts. The first part looks at the Switch execution plan operato...

Optimizer Limitations with Filtered Indexes One of the filtered index use cases mentioned in the product documentation ...

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...