Computer Bugs

Curious Partition Function Behaviour

imageJust another short blog today describing a curious issue I found with a query plan this week and a “workaround”.

In our core system, we have a table with two partitions. One partition contains all the work that “has been done” (which has the column WorkItem set to –1) and the other the “work   in progress” (with WorkItem to different values, all > -1).

The reason we have created just two partitions for this table (which is a heap) is that the items that are “work in progress” are often scanned, yet the work that has been done (WorkItem = –1) is the vast majority of the table. This “mini partitioning” is a nice design pattern I often apply to skewed distribution like these. It provides a significant performance boost on table scans. But this week I saw an oddity I have not run into before.

 

To repro the issue we saw on our server, let me create a simple repro with this script (the actual table has tens of millions of rows):

The query that the simple partition function is meant to serve is:

You would expect this query to only touch the second partition above, right? Wrong! Here is the actual plan:

imageimage

Both partitions are touched. What the heck is going on here?

After an initial bafflement (I know this is supposed to work), I rewrote the query to:

And look at this:

imageimage

The elimination happens as expected with this filter. In other words: the optimiser does not know that the following is true for integers:

x > –1 <=> x >= 0

Note: This server is running 2008R2 on the latest CU.