Curious Partition Function Behaviour
Just 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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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:
|
1 2 |
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:
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:
|
1 2 |
SELECT * FROM Foo WHERE WorkItem >= 0 |
And look at this:
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.
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…
Pingback: (SFTW) SQL Server Links 17/01/14 - SQL Server - SQL Server - Toad World
Pingback: (SFTW) SQL Server Links 17/01/14 • John Sansom
So many great blog posts lately. Have you been fired, Thomas?! 😉
Far from it I am afraid, I have been unable to sleep at night for being too busy 🙂
Best regards -TK
Well, thank god our jobs are also our hobby. What a great place to be.
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? )
I dont think have enough bugs for an entire section. Most of the time, the optimiser is kind of OK 🙂
Similar to filtered indexes – predicates may have to be very explicit, otherwise the optimiser ignores them…
Yes, its a of a WTF moment