Introducing to the SQL Server Columnstore Indexes

 

    Columnstore Indexes introduced in SQL Server 2012 version, Microsoft has been working hard to make columnstore indexes better with every new version of SQL Server. Columnstore indexes are great but they are not for OLTP databases. Columnstore indexes are designed specially for analytic databases. You want to learn more about columnstore indexes if you have data warehouse or data mart databases in your environment.

   Columnstore indexes can make your analytic queries pretty fast.  According to Microsoft, columnstore indexes can achieve up to 10x query performance. Columnstore indexes compress data which means you gain disk space by creating them. How much space? Up to 10x  data compression over uncompressed data.

    Let's look at how columnstore indexes does the magic to accomplish 10x query performance and 10x disk space.

    1. Columns store values from the same domain in a table and values are very similar to each other. Compressing similar values results high compression. This minimizes the IO bottleneck and reduces the memory foot printing.
    2. Reduced memory foot-printing causes queries to run faster since there is more space in the memory to perform more queries and data operations.

You should consider using columnstore indexes in your datawarehouse / datamart, you will see significant changes in the query performances. Your reports will run faster and you will gain disk space.

SQL Server 2016 Updates

    As I said earlier, Microsoft has been working to make columnstore indexes better. There are some big changes in SQL Server 2016 version too.

    SQL Server 2016 introduced real-time operational analytics, this is huge because that means you can run both analytics and OLTP workload from the same database tables in the same time! Also with this feature you can eliminate your data warehouse and  ETL which copies data from OLTP to your data-warehouse.

   Real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index creates a copy of the data so the OLTP and analytics workloads run against separate copies of the data. SQL Server keeps maintaining index changes so OLTP changes are always up-to-date for analytics. That means real-time analytics, business does not need to wait for ETL to run so they can see the data!