Design question on Columnstore indexing implementation.

Question

Design question on Columnstore indexing implementation.

VoldemarG

Hall of Fame

Points: 3883
More actions
April 22, 2020 at 5:57 pm

#3745425

If my table has 1 Clustered Index (PK), and a dozen of non-clustered row-store indexes.
I drop constraint... (existing PK/Clustered Index), Create Clustered Columnstore index on the table.
After this should I drop all my existing rowstore non-clustered indexes or should I still use them and they in fact are expected to work ? Or should I replace each of them with Non-Clustered Columnstore indexes, same columns as rowstore non-clustered indexes?..

Thank you.
Likes to play Chess

Viewing 7 posts - 1 through 6 (of 6 total)

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

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

you should have the indexes that are required to support your queries - having them as Columnstore or not will depend on your needs and on your testing that have show that a particular index, of a particular type, was required.

As with everything else you need to test test test.

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

Oops. Wrong window.

This reply was modified 4 years ago by Grant Fritchey.
This reply was modified 4 years ago by Grant Fritchey.

"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 3

VoldemarG wrote:

If my table has 1 Clustered Index (PK), and a dozen of non-clustered row-store indexes.
I drop constraint... (existing PK/Clustered Index), Create Clustered Columnstore index on the table.
After this should I drop all my existing rowstore non-clustered indexes or should I still use them and they in fact are expected to work ? Or should I replace each of them with Non-Clustered Columnstore indexes, same columns as rowstore non-clustered indexes?..
Thank you.

Regardless of whether you want to keep the NCIs or not, dropping the existing constraint will drop the CI and cause ALL of the NCIs to be rebuilt. If you keep the NCIs and add a new PK constraint to the new CI, the NCIs will be rebuilt again. I strongly recommend that you disable the NCIs before dropping the existing constraint on the CI and then rebuild them to re-enable them AFTER the new constraint has been established. That way, the NCIs will only need to rebuild once each.

--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)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

Regardig columnstore index: When I replace the Clustered index with Columnstore Clustered index (CCSI),

There are 20 other Non-clustered regular rowstore indexes on same table. They are still there. Should I drop them all and just use one CCSI only expecting all queries that used to use NCIs be working with new CCSI ?

Or should I just leave all them 20 NCIs there ?

Likes to play Chess

frederico_fonseca SSCoach Points: 16014 More actions · Answer 5

as I told you above you need to test your system and see if they are required.

Nothing we may say should influence you on that - we may tell you to drop all of them only for you to find that most of your queries behave badly and have your users screaming at you because the system is slow.

you need to TEST ... multiple times.

VoldemarG Hall of Fame Points: 3883 More actions · Answer 6

yes, I understand, thank you. But what do you test when you have

4000 transactions going all day and may be a 100 SPs that hit the table that got compressed ? It may take quite a while to track any performance trends, right? I ran may be a dozen queries before and after, but should it be enough to assume that a 1000 times bigger workload will behave the same?

Likes to play Chess