Design question on Columnstore indexing implementation.

  • VoldemarG

    Hall of Fame

    Points: 3672

    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.

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14686

    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: 396563

    Oops. Wrong window.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 996676

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • VoldemarG

    Hall of Fame

    Points: 3672

    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 ?

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14686

    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: 3672

    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?

    Voldemar likes to play CHESS (and IS good at it!)

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

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