Home > Uncategorized > Curious Partition Function Behaviour

Curious Partition Function Behaviour

January 15, 2014 Leave a comment Go to comments

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):

CREATE PARTITION FUNCTION pf_test(INT)
AS RANGE RIGHT FOR VALUES (0)

CREATE PARTITION SCHEME ps_test
AS PARTITION pf_test ALL TO ([DATA])

CREATE TABLE Foo
(
    WorkItem INT NOT NULL
    , Payload CHAR(300) NOT NULL DEFAULT REPLICATE (‘X’, 300)
) ON ps_test(WorkItem)

INSERT INTO Foo (WorkItem) VALUES (-1)
INSERT INTO Foo (WorkItem) VALUES (-1)
INSERT INTO Foo (WorkItem) VALUES (2)
INSERT INTO Foo (WorkItem) VALUES (1)
INSERT INTO Foo (WorkItem) VALUES (1)
INSERT INTO Foo (WorkItem) VALUES (2)

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

SELECT * FROM Foo
WHERE WorkItem > –1

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:

SELECT * FROM Foo
WHERE WorkItem >= 0

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.

Advertisements
  1. John Alan
    January 16, 2014 at 08:45

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

  2. January 16, 2014 at 10:01

    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? )

    • January 16, 2014 at 11:41

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

  3. January 16, 2014 at 16:57

    So many great blog posts lately. Have you been fired, Thomas?! 😉

    • January 16, 2014 at 16:58

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

      Best regards -TK

      • January 16, 2014 at 17:22

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

  4. Parker
    January 21, 2014 at 12:29

    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…

  1. January 17, 2014 at 09:22
  2. January 17, 2014 at 10:02

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s