Clustered Columnstore Index and Partition Elimination
-
I have a clustered columnstore index that is partitioned on a date key. If I write a simple query that filters on the partition key, I can see in the query plan results that I achieved partition elimination:
Here it shows that 8 partitions were accessed.
I also have a much more complex query - I cannot post the actual query here, but the general setup is as follows:
SELECT ...
FROM (
SELECT groupingcol, SUM(col1), SUM(col2), ...
FROM Fact1
WHERE ...
UNION ALL
SELECT groupingcol, SUM(col1), SUM(col2), ...
FROM Fact2
WHERE ...
SELECT groupingcol, SUM(col1), SUM(col2), ...
FROM Fact3
WHERE ...
)
JOIN someothertables...
The where clause in the sub-select also filters on the date partition key, but when I look at the query plan I see this:
Since "Actual Partition Count" is 0, does this mean I'm not achieving partition elimination, even though it's showing the expected seek predicate? We're on SQL Server 2016 (v 13.0.5081.1), but running on compatibility mode 120
- This topic was modified 5 years ago by chris.o.smith.
-
I don't think so, I think 0 should mean your query accessed no partitions. If it had accessed all of them, the total number of partitions should be there.
Review the actual query plan and make sure nothing is somehow eliminating all the partitions from being read.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I see, thanks - that was the problem!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply