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

  • 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/

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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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]

  • 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/

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

  • 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/

  • 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

  • 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/

  • 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

  • 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/

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

    Gerald Britton, Pluralsight courses

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

  • 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

  • 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/

  • 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 21 total)

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