Partitioning An Existing Table With A Columnstore Index ?

Question

Partitioning An Existing Table With A Columnstore Index ?

info 58414

Hall of Fame

Points: 3386
More actions
August 14, 2018 at 2:41 am

#377482

Hi,
does it make sense to additionally partition a table (FactTable with 5-10 million rows) with a clustered columnstore index? Example: Partition by Year + Month in separate Filegroups / Files?
Or is the Clustered Columnstore Index with any NCX enough?
Thanks
Regards
Nicole
:ermm:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

Are you partitioning in order to take advantage of the data management aspects of partitioning (rolling windows, that sort of thing) or are you attempting to partition for performance. If the latter, don't.

As for columnstore, it's incredible and amazing... if you're running analysis style queries. If you're running primarily OLTP-style point look-ups, columnstore is a very bad choice.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Grant Fritchey - Wednesday, August 15, 2018 5:12 AM
Are you partitioning in order to take advantage of the data management aspects of partitioning (rolling windows, that sort of thing) or are you attempting to partition for performance. If the latter, don't.
As for columnstore, it's incredible and amazing... if you're running analysis style queries. If you're running primarily OLTP-style point look-ups, columnstore is a very bad choice.

Not having had the opportunity to play with ColumnStore, yet, please pardon the ignorant question on my part. Isn't it true that ColumnStore requires you to dedicate memory to just that one table and that memory can't actually be used by anything else in the face of "memory pressure" required by any other process?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Jeff Moden - Wednesday, August 15, 2018 8:44 AM
Not having had the opportunity to play with ColumnStore, yet, please pardon the ignorant question on my part. Isn't it true that ColumnStore requires you to dedicate memory to just that one table and that memory can't actually be used by anything else in the face of "memory pressure" required by any other process?

Nope. That's a bleed-over from in-memory tables, and, the unfortunate side effect from the damned marketing department (all marketing is evil) at Microsoft tacking in-memory to columnstore when it first got released. Columnstore indexes compete for memory resources same as all the other indexes. They aren't part of the in-memory behaviors, just part of the marketing.