SQL 2014: Clustered Columnstore Indexes

Well…here are my first thoughts on the new clustered columnstore indexes in SQL Server 2014.

SQL2014-my-first-thought-on-the-new-clustered-columnstore-indexes

The SQL Server in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing. Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.

‘Columnstore Indexes Described’ on MSDN:
http://msdn.microsoft.com/en-us/library/gg492088.aspx


I’ve read that one before 🙂
So…a bit oversimplified perhaps,but….SQL2014 minus SQL2012 equals ‘clustered columnstore indexes’?
Naaahhh….there’s more! SQL Server 2014 – InMemory Tables, Indexes and Stored Procedures


Jeremiah Peschka about the topic
http://www.brentozar.com/archive/2014/06/memory-analytics-whats-different-sql-server-oracle/

SQL Server 2014 columnstore fixed the read-only problem by introducing a writable clustered columnstore. When the index is created as a clustered columnstore index (it’s the only index on the table) SQL Server will use a writable deltastore to hold data. This advantage over SQL Server 2012 has prompted many teams to migrate to SQL Server 2014.

The downside of both SQL Server columnstore implementations is that they’re designed for pure data warehouse workloads. If you wanted to combine an OLTP workload with a data warehouse workload on the same system, you’d have some work head of you to build something that would work with both regular indexes and columnstore indexes. At its core, SQL Server columnstore technology is a data warehouse technology.



Too bad that clustered columnstore indexes are only available in the Developer (whoohoo) and Enterprise editions of SQL Server 2014.


COMPARING IN-MEMORY ANALYTICS
While both companies have produced an in-memory analytic feature of one form or another, direct comparisons are difficult. Microsoft SQL Server columnstore indexes are designed to improve pure data warehouse implementations. Oracle Database In-Memory, on the other hand, is intended to help DBAs get the most out of the hardware they already have. Many business users want to report on live data and this feature makes live reporting a possibility.



…and it’s way cheaper 😀


Worth upgrading?
The ‘What’s new’ list for SQL Server 2014 is dominated by the in-memory OLTP feature, which can deliver remarkable performance benefits, provided that you have the Enterprise edition and a suitable application. This makes it a must-have upgrade for those who can use it, but less compelling otherwise, although improved Azure integration is also a significant benefit.
The updateable clustered columnstore index is an important data warehousing feature. Another feature worth a mention is the Buffer Pool Extension, which lets you use SSD (Solid State Drive) storage as extra memory.

inmemory

In-memory OLTP
In-memory OLTP, combined with native code stored procedures, can deliver remarkable performance improvements.
The core of SQL Server is little changed in this release, and while that is no bad thing considering what a solid product it is, it does make this a minor release in the event that you are not in a position to make good use of the big new features – especially for Standard edition users for whom some new features do not apply.
None of this takes away from the excellence of the product, which has a broad and deep range of features as well as strong performance.

http://www.greymatter.com/corporate/hardcopy-article/sql-server-2014/



More technical info about columnstore indexes:
http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features
http://blogs.askcts.com/2014/06/27/sql-server-2014-columnstore-indexes/



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group








Advertisements

2 thoughts on “SQL 2014: Clustered Columnstore Indexes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s