Environment is SQL 2016/SP1/CU1 Enterprise on Windows 2012 R2 Standard, and I'm running tests on using paritioning in combination with clustered columnstore.

For one large table (300 million rows), data was first sorted by a date column, bulk inserted into the partitioned table, and then a clustered columnstore index was created.

The data type for the partitioning column is date, and this column exists in the smaller parent table, and the larger partitioned table.

There are a few joins from the large table to other smaller tables.

When the predicate is: WHERE parent.datecolumn BETWEEN @date1 and @date2

I see rowgroup elimination, but no partition elimination.

When the predicate is: WHERE parent.datecolumn BETWEEN @date1 and @date2 AND partitioned.datecolumn BETWEEN @date1 and @date2

I do not see rowgroup elimination, but I do see partition elimination.

I'm not sure if this is expected behavior, but didn't entirely make sense to me.

  • Also wanted to mention that in both cases, for the large table the columnstore index was used, with batch mode. – NedOtter Mar 24 '17 at 14:20
  • Do you get partition elimination if you have a "normal" Clustered index? I've also seen SQL Server to be particularly picky about data types in this area. I had to cast constants to get it to work. – Henrik Staun Poulsen Mar 24 '17 at 15:05
  • Not a lot to go on without query plans, etc. Can you post those anywhere, like brentozar.com/pastetheplan (Full disclosure, I work for BOU) – Erik Darling Mar 24 '17 at 15:08
  • Thanks for your help, folks. HenrikStaunPoulsen - I did not test a regular clustered index. Need it to work with clustered columnstore. spBlitzErik - I would need permission to post plans, and will check on that. – NedOtter Mar 24 '17 at 15:12
  • @sp_blitzErik here's a link to the execution plan, based on 2016 WideWorldImporters OLTP (full). nedotter.com/ned/Otter_PartitioningColumnstoreRepro.sqlplan Thanks for your help-- – NedOtter Mar 25 '17 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.