Page Free Space

Rewriting Queries to Improve Performance

Though SQL is a declarative language, expressing queries differently can dramatically change execution plans.

The 2008 R2 Bug Fix That Breaks RCSI

Details of a regression in a deadlock fix.

A Subquery Cardinality Estimation Bug

A SQL Server semi join cardinality estimation bug where predicates pushed beyond a UNION ALL are missed.

SQL Server Isolation Levels: The Series

Index to my series on concurrency, the ACID properties of statements and transactions, and isolation levels in SQL Server.

The Snapshot Isolation Level

Guarantees provided by the SQL Server snapshot isolation level and some of its surprising behaviours.

An Unexpected Side-Effect of Adding a Filtered Index

Adding a filtered index can have surprising side-effects on existing queries, even where it seems the new filtered index is completely unrelated.

Data Modifications under Read Committed Snapshot Isolation

Why and when UPDATE and DELETE statements acquire locks under RCSI.

Read Committed Snapshot Isolation

The detailed behaviour of RCSI compared with locking read committed isolation, including subtle aspects to be aware of.

The Read Committed Isolation Level

The logical and physical guarantees provided by read committed isolation.

The Repeatable Read Isolation Level

The guarantees provided by the repeatable read isolation level, including some allowed behaviours you might find surprising.

Cardinality Estimation for Disjunctive (OR) Predicates

The cardinality estimation formulas for multiple OR predicates in SQL Server 2014 and later (using the 'default' cardinality estimator).

The Serializable Isolation Level

Guarantees provided by the strongest transaction isolation level and what they mean in practice.

The ACID Properties of Statements & Transactions

Concurrency considerations and the practical meaning of each component of the ACID acronym in a SQL Server context.

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.