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.

  10Comments

  1. Parker   •  

    Maybe for some reason the optimizer is not looking at this from an integer perspective?

    Or maybe there’s an integer between 0 and -1 that we just don’t know about yet…

  2. Pingback: (SFTW) SQL Server Links 17/01/14 - SQL Server - SQL Server - Toad World

  3. Pingback: (SFTW) SQL Server Links 17/01/14 • John Sansom

  4. tobi   •  

    So many great blog posts lately. Have you been fired, Thomas?! ;-)

    • Thomas Kejser   •  

      Far from it I am afraid, I have been unable to sleep at night for being too busy :)

      Best regards -TK

      • tobi   •  

        Well, thank god our jobs are also our hobby. What a great place to be.

  5. Feodor Georgiev   •  

    I like you sense of humor and the way you put this post as ‘Uncategorized’. :) (would not there be a whole ‘Bugs’ category, anyway, for the optimizer issues? )

    • Thomas Kejser   •  

      I dont think have enough bugs for an entire section. Most of the time, the optimiser is kind of OK :)

  6. John Alan   •  

    Similar to filtered indexes – predicates may have to be very explicit, otherwise the optimiser ignores them…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">