Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Comments posted to this topic are about the item Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Phil Parkin

    SSC Guru

    Points: 244107

    Thank you Hugo. Looking forward to the rest of the series.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Sean Woehrle

    Mr or Mrs. 500

    Points: 539

    Hugo,

    This is a great article. Just FYI we skipped over 2012 for column store indexes since we didn't want to drop and rebuild the indexes as part of data loading. The 2014 feature of updateable CCI (clustered column store index - covering) made us revisit the features.

    Thanks,

    Sean

    [font="Tahoma"]Cheers,

    Sean :-D[/font]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Sean Woehrle (2/25/2015)


    Hugo,

    This is a great article. Just FYI we skipped over 2012 for column store indexes since we didn't want to drop and rebuild the indexes as part of data loading. The 2014 feature of updateable CCI (clustered column store index - covering) made us revisit the features.

    Thanks,

    Sean

    Hi Sean,

    The uptake of columnstore in SQL2012 was very limited, and the read-only restriction is a very large contributing factor to that.

    However, there ARE alternatives to dropping and rebuilding the index, to make the loading process slightly less painful. I will cover these in one of the later steps.

    Cheers,

    Hugo


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • bill-kline

    SSC Enthusiast

    Points: 169

    Has the Database Tuning Advisor been updated to potentially recommend columstore indexes?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    bill-kline (2/25/2015)


    Has the Database Tuning Advisor been updated to potentially recommend columstore indexes?

    No.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Vijay.Govindan

    SSC Enthusiast

    Points: 136

    Hi Hugo,

    Thanks for the article pointing out the savings related to I/O and Batch mode. Have you done any tests on Updates / Deletes/ Inserts with and without a nonclustered columnstore? Look forward to reading your second article.

    Vijay

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Vijay.Govindan (4/30/2015)


    Hi Hugo,

    Thanks for the article pointing out the savings related to I/O and Batch mode. Have you done any tests on Updates / Deletes/ Inserts with and without a nonclustered columnstore? Look forward to reading your second article.

    Vijay

    Hi Vijay,

    Thanks for your kind words! The second article has in fact just been published (link: http://www.sqlservercentral.com/articles/Stairway+Series/124326/[/url]).

    Adding a nonclustered columnstore index to a table has the effect of making the table read-only. A clustered columnstore index (introduced in SQL Server 2014) does not have that drawback, but the cost of modifications is higher than for traditional indexes. I will cover that later in the series.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Vijay.Govindan

    SSC Enthusiast

    Points: 136

    Hi Hugo,

    You are welcome! Yes, I forgot about that, being read only limits its use in a Datawarehouse / ETL environment in our case. Thanks for the link, I will be sure to check it out.

    We just started deploying filtered indexes in some of our ETL tables to help speed up our nightly loads since we are still using 2008 R2. We've seen dramatic improvements and I wanted to compare the filtered indexes against the clustered columnstore indexes and see which is faster overall. Thanks for your prompt and courteous response!

    Vijay

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Vijay.Govindan (4/30/2015)


    Hi Hugo,

    You are welcome! Yes, I forgot about that, being read only limits its use in a Datawarehouse / ETL environment in our case. Thanks for the link, I will be sure to check it out.

    We just started deploying filtered indexes in some of our ETL tables to help speed up our nightly loads since we are still using 2008 R2. We've seen dramatic improvements and I wanted to compare the filtered indexes against the clustered columnstore indexes and see which is faster overall. Thanks for your prompt and courteous response!

    Vijay

    If you have a datawarehouse that uses table partitioning, then the read-only limitation becomes far less annoying, because you can use partiition switching to add new data without having to rebuild the entire columnstore index. This, too, will be covered in a later level.

    And yes, depending on data and workload, filtered tables can also help. There are often multiple solution available in SQL Server.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • g.britton

    SSChampion

    Points: 13689

    Small spelling error in the tagline: "introdused" should be "introduced"

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Ed Pollack

    Hall of Fame

    Points: 3107

    An excellent, well-written stairway on a feature that I am very fond of. Thanks!

  • Y.B.

    SSChampion

    Points: 11549

    This is great Hugo!

    On a side note it might have been worth mentioning that you'll need an enterprise edition of SQL to try it out.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    yb751 (5/30/2016)


    This is great Hugo!

    On a side note it might have been worth mentioning that you'll need an enterprise edition of SQL to try it out.

    You are right, I should have added that.

    By the way, for testing it you can also use Developer Edition. That edition has all the features of Enterprise Edition without the cost (but obviously not licensed for production use). This used to be a bargain at I think 50 bucks or so, and recently Microsoft announced that Developer Edition will be completely free going forward. Not sure if that "completely free" applies to the SQL Server 2016 version only or also to older versions.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Y.B.

    SSChampion

    Points: 11549

    Hugo Kornelis (5/30/2016)


    yb751 (5/30/2016)


    This is great Hugo!

    On a side note it might have been worth mentioning that you'll need an enterprise edition of SQL to try it out.

    You are right, I should have added that.

    By the way, for testing it you can also use Developer Edition. That edition has all the features of Enterprise Edition without the cost (but obviously not licensed for production use). This used to be a bargain at I think 50 bucks or so, and recently Microsoft announced that Developer Edition will be completely free going forward. Not sure if that "completely free" applies to the SQL Server 2016 version only or also to older versions.

    I could be wrong but I believe the free developer edition only applies to 2014 & 2016. My local copy for testing is 2012 so that I can mirror my production environment. However, I may consider also installing 2014 just so I can at least test some of the features I currently don't have access to.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

Viewing 15 posts - 1 through 15 (of 20 total)

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