Page Free Space

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

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

Incorrect Results with Indexed Views If you use MERGE , indexed views and foreign keys, your queries might return incor...

A creative use of IGNORE_DUP_KEY Let’s say you have a big table with a clustered primary key, and an application that i...

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

Deletes that Split Pages and Forwarded Ghosts Can DELETE operations cause pages to split? Yes. It sounds counter-int...