Rereading Craig Freedman: the query processor blog that still holds up after 20 years
Between 2006 and 2010, Craig Freedman — then a developer on Microsoft's SQL Server Query Optimizer team — wrote what may well be the best series of blog posts ever published on query processing. No marketing fluff, but someone who knew the optimizer from the inside, explaining post by post why SQL Server does what it does: how iterators cooperate, why a hash join sometimes beats a merge join, what really happens under parallelism.
The blog now survives only as an archive on learn.microsoft.com, and that's a shame. So I reread the entire series — and more importantly: re-tested every concept on modern SQL Server versions, with real execution plans. The verdict after ten articles: virtually everything still stands. The query processor Freedman described in 2006 is, at its core, the same one building your plans today. Understand his series, and you'll read every execution plan with different eyes.
Below is the reading guide to all ten parts. New to execution plans? Just start at part 1 — the series is deliberately chronological. Looking for a specific topic — joins, parallelism, isolation levels — jump straight to the part that covers it.
2006: the foundations, from iterators to parallelism
- June 2006: The Foundations — The mental model everything rests on: the iterator tree, reading plans (graphical, text, XML), scans versus seeks and the infamous bookmark lookup. Required reading.
- July 2006: From Indexes to Joins — From index usage to the first join: how the nested loops join works and when the optimizer picks it.
- August 2006: Merge Join, Hash Join and Subqueries — The other two join operators, their memory behaviour, and how subqueries get rewritten into joins under the hood.
- September 2006: Aggregation — Stream aggregate versus hash aggregate: why sort order makes the difference and what that means for GROUP BY and DISTINCT.
- October 2006: Decorrelation, Index Union and the Exchange Operator — The optimizer as rewriter: correlated subqueries untangled, multiple indexes combined, and the exchange operator that makes parallelism possible.
- November 2006: Parallel Scans, Parallel Joins and Partitioned Tables — How SQL Server distributes work across threads: parallel scans and joins, and the interplay with partitioned tables.
- December 2006: The Semi-join Transformation — One transformation in depth: how EXISTS and IN are executed as semi-joins and why that beats rolling your own workaround.
2007–2010: from isolation levels to the final posts
- 2007: Isolation Levels, PIVOT, TOP, Index Maintenance and CTEs — The broadest year: what isolation levels actually guarantee, how PIVOT and TOP end up in the plan, index maintenance and recursive CTEs.
- 2008: Ranking Functions, Conversions, Partitioning and I/O — ROW_NUMBER and friends in the plan, the pitfalls of implicit conversions, partitioning, and how I/O makes or breaks a query.
- 2009–2010: I/O by Sorting, Hint Pitfalls and the Final Posts — The closing act: taming random I/O by sorting, why hints do more harm than good more often than not, and the final posts of a series that proved more than worth rereading.
Why this still matters
SQL Server has gained a lot since 2010 — columnstore, batch mode, Intelligent Query Processing, Azure SQL — but the row-mode operators Freedman wrote about still show up in nine out of ten plans I see as a DBA. A nested loops join works in SQL Server 2025 exactly the way it did in 2006. That's precisely what makes this series so valuable: it isn't version knowledge that ages, it's foundation knowledge that lasts.
The complete series is collected at dbaronald.nl/category/craig-freedman.
Stuck on an execution plan you can't explain? I'm happy to take a look.