Performance Tuning
Partitioned Views, Aggregates, and Cool Query Plans
The Max for the Minimum
Paul White (obviously of course as always) has a Great Post, Brent® about Aggregates on partitioned tables
Well, I’m not that smart or good looking, so I’ll have to settle for a So-So Post about this.
There are actually quite a few similarities between the way a partitioned table and a partitioned view handle these things.
Building on previous examples with the Votes table converted into a partitioned view, let’s try a couple queries out.
Selecting the global min and max give me this query plan.
|
1 2 |
SELECT MIN(v.CreationDate), MAX(v.CreationDate) FROM dbo.AllVotes AS v; |
And I know, it looks big and mean. Because it kind of is.
BUT LOOK HOW LITTLE WORK IT DOES!
Just like in Paul’s post that I linked to above, each one of the top operators is a TOP 1.
For the Min, you get a forward scan, and for the Max you get a backwards scan.
That happens once per table in the partitioned view.
Easier to visualize
If we focus on a single table, it’s easier to parse out.
|
1 2 3 4 5 |
SELECT MIN(v.CreationDate), MAX(v.CreationDate) FROM dbo.AllVotes AS v WHERE v.CreationDate >= '20140101' AND v.CreationDate < '20150101' AND 1 = ( SELECT 1 ); |
A lot of people may complain that there are two index accesses here, but since SQL Server doesn’t have anything quite like a Skip Scan where it could hit one of the index and then jump to the other end without reading everything in between, this is much more efficient.
Thanks for reading!
Brent says: remember, a scan doesn’t mean SQL Server read the whole table, and a seek doesn’t mean it only read a few rows. It’s so hard to tell this stuff at a glance in execution plans, especially in estimated plans.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Do you mind explaining what you need the ” AND 1 = ( SELECT 1 );” for?
It avoids trivial plans/simple parameterization.
hi Erik,
Thank you. Nice trick.
The problem with partitioned tables comes when you want to find max(ID) on dbo.Votes.
SQL Server is really bad at that, even if you create a separate index.
You have to create a NON-Alligned index (non-partitioned) before SQL Server can avoid table scans.
see https://dba.stackexchange.com/questions/117016/seek-and-you-shall-scan-on-partitioned-tables
Right, but this isn’t a partitioned table.
Yeah I complained about that once. It’s trivial to show that you can run down either the left or right side of the B-tree to get the max/min (depending on the order) for an indexed column. Yet SQL Server scans the whole thing anyway.
Can you expand on that a little? Or post a link?
Sure, here you go.
Thanks! I wonder though, is that “trivial” workaround worthy of a query hint?
Gerald – sure, if you think Microsoft should add a query hint that would continue optimizing past the trivial stage, post a Connect item and they might go for it. (I’m always surprised by what they choose to implement and what they don’t.)
So-So Post, Brent!
That’s it, I’m revoking his WordPress privileges.