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.

Markus Ehrenmüller-Jensen
@MEhrenmueller & @SQLederhose

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