Analysis of the Column-Store Index for Microsoft SQL Server 2016 (Part 1)
The experts at Microsoft advocate the use of the column store index for analytical workloads in the data warehouse. One of the reasons for this is clustered column store indices give significant compression over row store tables giving rise to increased space savings and query performance.
In addition the reason column store is advocated for OLAP workloads is that since OLAP tables tend to have more rows compared to OLTP tables that approximately 3x compression can be achieved on analytical workloads leading to significant gains in I/O and storage. Furthermore, it can be seen that when queries are run on fact tables with a large number of columns on OLAP workloads, typically only a subset of the columns are selected. If row store is used when data is selected even if a subset of the columns are used in the SELECT query, entire rows of data have to be scanned. However, in column store, the selection of a subset of columns ensures that only the relevant columns are selected thereby further reducing I/O and improving query performance.
Microsoft claims that their column store engine is the industry leading standard due to it’s approach of using compression, selection of column subsets and batch processing mode.
The difference in Data Warehouse (DW) architecture in SQL Server 2016 is that when using SQL Server Analysis Services (SSAS) using Direct Query mode it is possible to access the column-store relational DW allowing to access the data as soon as it is updated. However, it should be noted that since Multi-Dimensional queries are on pre-aggregated cubes while they could give a faster query performance, the chances are high that the Tabular model will give more up-to date data. So it should be noted that you should balance speed vs the most up-to-date version of the data being available when deciding between multi-dimensional cubes and the tabular mode.
So that gives you a basic introduction about the high level features in Microsoft SQL Server 2016 with regards to the column-store index. I plan to write more on the detailed workings in the remaining parts! Stay tuned!
Please click 👏 button below a few times to show your support! ⬇⬇ Thanks! Don’t forget to follow Quick Code below.
Find out Free courses on Quick Code for various programming languages. Get new updates on Messenger.
