February 25, 2015 at 12:00 am
Comments posted to this topic are about the item Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes
February 25, 2015 at 3:00 am
Thank you Hugo. Looking forward to the rest of the series.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 25, 2015 at 7:44 am
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]
February 25, 2015 at 8:53 am
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
February 25, 2015 at 12:53 pm
Has the Database Tuning Advisor been updated to potentially recommend columstore indexes?
February 25, 2015 at 4:34 pm
bill-kline (2/25/2015)
Has the Database Tuning Advisor been updated to potentially recommend columstore indexes?
No.
April 30, 2015 at 2:38 pm
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
April 30, 2015 at 3:05 pm
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.
April 30, 2015 at 3:18 pm
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
April 30, 2015 at 4:09 pm
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.
June 2, 2015 at 1:42 pm
Small spelling error in the tagline: "introdused" should be "introduced"
Gerald Britton, Pluralsight courses
June 10, 2015 at 6:07 am
An excellent, well-written stairway on a feature that I am very fond of. Thanks!
May 30, 2016 at 12:06 pm
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.
May 30, 2016 at 1:41 pm
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.
June 1, 2016 at 7:33 am
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.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply