Boosting DWH Performance with SQL Server 2016 at SQL Saturday Sofia 2015

I was very glad to return to Bulgaria within a couple of months to attend the SQL Saturday Sofia 2015 and beeing accepted for two talks. One of them covering how to use the improvements for ColumnStore technology in SQL Server 2016 to boost the performance of your DWH.

I was very excited when the xVelocity technology was introduced into both the relational database of SQL Server (as Non-Clustered ColumnStore Index) and Analysis Services (as Tabular model). As I was already stunned by the features available with the Power Pivot add-in (available since Excel 2010), this was a huge step, integrating a whole different way of storing direct into the heart of the database. (As opposed to the other vendors, which came up with vertical storage as well, but as another tool and not integrated in their core applications.)

SQL Server 2012 had unfortunately a lot of restrictions for the ColumnStore. The biggest of them are, that the index is available as a Non-Clustered Index only, and the the table becomes read-only.
SQL Server 2014 came up with lesser restrictions and introduced a Clustered ColumnStore Index, which is update-able, but letting the Non-Clustered Index still read-only.

For SQL Server 2016 we expect a whole bunch of improvements:

  • Less restrictions and performance-improvements to Non-Clustered & Clustered ColumnStore Indexes
  • Update-able Non-Clustered ColumnStore Index
  • Creating Non-Clustered B-tree Indexes on tables with Clustered ColumnStore Index
  • Creating ColumnStore Indexes on In-Memory OLTP tables

You can find my slide deck here.

Sincerly,
Markus Ehrenmüller-Jensen
@MEhrenmueller & @SQLederhose
markus.ehrenmueller@gmail.com

Boosting DWH Performance with SQL Server 2016 at SQL Saturday Sofia 2015

ColumnStore Index Best Practice at SQLdays

Last week I did two talks at SQLdays, Erding, Germany. One of them was about “ColumnStore Index Best Practice”. I basically set the frame by using a three-folded agenda:

  • ColumnStore
  • Non-Clustered ColumnStore Index
  • Clustered ColumnStore Index

It is important to understand the basic concepts of a ColumnStore, as opposed to the way data in a relational database was stored over the last decades. The latter is now called RowStore and didn’t have a special name until ColumnStore was introduced, because it was the one and only way how to store data.

After getting behind the “magic” of a ColumnStore it is much more easier to evaluate the use cases, where it is appropriate, and what to consider when maintaining this special kind of “index”. (Even the definition of the word “index” gets a new point of view, as rows in a ColumnStore are not sorted – which is conversed to a b-tree index.)

Non-Clustered ColumnStore Index was introduced in SQL Server 2012 and came with a whole list of limitations. One of the most discussed limitation was, that the table for which the Non-Clustered ColumnStore Index was created, becomes read-only.

Clustered ColumnStore Index was introduced in SQL Server 2014 and came with a better performance and a shorter list of limitations. One of the limitations we got rid of was, that the table for which a Clustered ColumnStore Index was created, was still updateable. (But in SQL 2014 tables with a Non-Clustered ColumnStore Index is still read-only).

You can find my slide deck here.

Sincerly,
Markus Ehrenmüller-Jensen
@MEhrenmueller & @SQLederhose
markus.ehrenmueller@gmail.com

ColumnStore Index Best Practice at SQLdays