An Introduction to Partition Elimination

Horizontal Table and Index Partitioning, where different rows in the same logical table are split into different physical tables, is an essential data warehousing tool.  It allows large tables to be split into multiple physical heaps or b-trees based on a key in the data.  This often makes big tables more manageable because many operations […]

DBCC MEMORYSTATUS – Memory objects and the SQL Server Query Optimizer

So one of the features I had a hand in building in SQL Server 2005 is really useful for helping SQL Server work well in large data warehouse deployments. I led a team writing the paper that was published about this, and you can read it in my previous blog post here.  The paper covers […]

A Call for Data Warehouse War Stories…

One of the reasons I’m posting up content like this is to help all of you learn a bit of what I have bouncing around my head.  I’m hoping that I’ll get a few more people writing some awesome data-driven applications. So, I’d like to get some help from you – I’d like to hear […]

Operator of the Day: Sequence Project

So I’ll be posting explanations for each query operator in the output query trees that you can see through “set showplan_text on” in SQL Server.  I’m hopeful that this will give you the tools to better read the query plans being generated by the QP when evaluating your system. So I’ll start with one that […]

SQL Server Query Optimization and Data Warehouses

For a number of years, the marketing for SQL Server’s competitors made a large number of claims that SQL Server couldn’t handle “Enterprise”-scale deployments.  Over time, those claims became much quieter as Microsoft used SQL Server extensively to run their whole business, through multi-terrabyte reference cases, and the hugely successful TerraServer project where Jim Gray […]

Subqueries in the Query Optimizer

I’ve been trying to think of a good way to introduce how SQL Server does subqueries based on a question from a reader.  One of the tricky pieces to query optimization is that the “language” used internally in the optimizer is actually somewhat more expressive than SQL.  The second part of the problem is that, […]

SQL Server IN Plans and the query plan cache

This post builds upon my previous post on some of the various kinds of plans for the IN operator.  I will cover some of the query plan cache behavior associated with IN plans. For this post we’ll be using the following query which will let you look at the text of plans in the cache […]


In this post, I’ll go over a few things about IN plans in the SQL Server Query Optimizer.  Currently I’m using the SQL Server 2008 November CTP, but this behavior is still valid on SQL 2005. I’ll be using the following script for my discussion, and you can try this on your own. create database […]

User Question: Plan Cache Pollution

I’ve already received a few comments and mails from people asking questions… One comment I’ve received is about LINQ’s IN operator(method?) and SQL Server plan cache pollution.  I’m researching the issue a bit before I post a complete answer, but I’ll start with some background information on memory management in SQL Server.  I co-authored a […]

Howdy! Come on in!

Here is the introductory post to my new blog on SQL, data-driven applications, performance, scaling, and anything else I feel like posting.  For those of you who do not know me, Paul Randal ( and I worked together on Microsoft SQL Server for a number of years.  While Paul worked in the Storage Engine, I […]