August 16, 2018 at 12:13 pm
Hi,
I am new to this forum and have a question. We have been evaluating a clustered column store index on a large fact table (170 million rows). We are comparing preproduction environment to production. We replaced our clustered row store index on posted delivery date with a clustered row store with maxdop set to 1.
I understand that aggregated queries are supposed to perform better, but do not see this consistently. The example of one such query is attached along with explain plans for both and the query. Could someone help to explain why this aggregated query does not perform as well as its rowstore index counterpart? Any help would be appreciated as we are about to abandon pursuing clustered col store.
Thanks,
Peter.
August 16, 2018 at 1:26 pm
Did you partition the columnstore clustered index on posted delivery date also? That would make sense if it was best to cluster the row store on that column. It's important with columnstore index to eliminate partitions where possible.
You'd want each partition to be at least 1M rows, but that shouldn't be an issue when you have 170M rows in the table. Much trickier to decide if you want to limit the total rows in a single partition, because that might require you to use weeks, say, rather than months to partition on, which people are just not as used to seeing.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply