Columnstore Index - List of blogs on columnstore index published by SQL Server Product Team

SQL product team has made significant improvements in functionality, supportability and performance of columnstore indexes during SQL Server 2016. These improvements were prioritized based on the feedback by customer who had used columnstore index functionality in prior releases.

SQL Server product team has created a large set of blogs across multiple columnstore index scenarios. This blog consolidates the links as a one stop shop to access these. We will keep updating it as new blogs are added.  If you identify areas of columnstore that needs additional information, please feel free to contact us for suggestions

Columnstore index

• Short video on columnstore  https://www.youtube.com/watch?v=EB0MzVdc-ZI
• Channel 9 - columnstore technology https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-ColumnStore-Technology
• Why columnstore Index: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/13/columnstore-index-why-do-we-need-it/
• How much is the storage savings: https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-how-to-estimate-compression-savings/
• How does columnstore speeds up analytics https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/14/columnstore-index-how-does-sql-server-delivers-industry-leading-performance-for-analytic-queries/
• What is new in SQL Server 2016 https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/13/columnstore-index-what-is-new-in-sql-server-2016/
• Columnstore index and transaction isolation levels: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2014/07/26/clustered-column-store-index-concurrency-and-isolation-levels/
• concurrency and Insert Operation: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2014/07/27/clustered-column-store-index-concurrency-with-insert-operations/
• Scan and Parallelism https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/02/28/columnstore-index-scan-and-parallelism/
• Columnstore index and rowgroup sizes: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2014/05/03/clustered-column-store-factors-that-impact-size-of-a-rowgroup/
• Enforcing uniqueness constraint in clustered columnstore index https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/09/14/clustered-columnstore-index-enforcing-uniqueness-constraint-in-table/
• Difference between columnstore index vs Btree Index: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/17/columnstore-index-how-do-they-defer-from-traditional-btree-indices-on-rowstore-tables/
• Which columnstore index is right for me?https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/07/columnstore-index-which-columnstore-index-is-right-for-my-workload/
• Loading data from staging table https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/19/columnstore-index-parallel-load-into-clustered-columnstore-index-from-staging-table/
• Columnstore success stories https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/10/04/columnstore-index-in-memory-analytics-i-e-columnstore-index-videos-from-ignite-2016/
• Columnstore index in lower editions https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/19/columnstore-index-standard-and-express-editions-with-sql-server-2016-sp1/
• Why do we call it In-Memory analytics https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/07/columnstore-index-why-do-we-refer-to-it-as-in-memory-analytics/
• Should I partition my columnstore https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/10/04/columnstore-index-should-i-partition-my-columnstore-index/
• Columnstore and Stats https://www.sqlserverfaq.net/2016/03/11/column-store-indexes-and-statistics/
• Columnstore Index is now supported on Standard and Express Editions - https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/19/columnstore-index-standard-and-express-editions-with-sql-server-2016-sp1/
• How do I estimate compression savings?  https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-how-do-i-find-tables-that-can-benefit-from-clustered-columnstore-index/ 
• How do I know which tables I should create CCI to? https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-how-to-estimate-compression-savings/

 

Columnstore Index Presentations at conferences

• Clustered Columnstore Index https://www.youtube.com/watch?v=CTtzyyX3HQA
• Updateable NCCI Real Time Operational Analytics in SQL Server 2016
• Columnstore success stories https://www.youtube.com/watch?v=h_CspR86SB8

 

Columnstore index Performance

• Columnstore index value proposition - https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/13/columnstore-index-why-do-we-need-it/
• Column elimination - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-column-elimination/
• Rowgroup elimination - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-rowgroup-elimination/
• Batch mode execution - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-batchmode-execution/
• Aggregate pushdown - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-aggregate-pushdown/
• String predicate pushdown https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-string-predicate-pushdown/
• Multiple aggregates - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-multiple-aggregates/
• Batch mode Windows aggregates - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-window-aggregates-in-batchmode/
• No more Performance Cliff - https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-sql-server-2016-no-performance-cliff/
• DMV Performance Improvement: https://blogs.msdn.microsoft.com/sql_server_team/columnstore-dmv-performance-sql-server-2016-sp1-cu1-improved-performance-of-sys-dm_db_column_store_row_group_physical_stats/

Bulk importing data

• Basic Bulk Import Operations: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2014/07/27/clustered-column-store-index-bulk-loading-the-data/
• Parallel Bulk Import https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/02/28/clustered-columnstore-index-parallel-bulk-import/
• Bulk Import with Minimal Logging https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/01/10/clustered-columnstore-index-data-load-optimizations-minimal-logging/
• SSIS and Columnstore https://blogs.msdn.microsoft.com/sqlcat/2016/02/29/sql-server-2016-ssis-data-flow-buffer-auto-sizing-capability-benefits-data-loading-on-clustered-columnstore-tables/

JSON in Columnstore Index

• JSON support with columnstore index https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/09/json-data-in-clustered-column-store-indexes/
• Extreme 25x compression of JSON data using CLUSTERED COLUMNSTORE INDEXES https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/09/extreme-25x-compression-of-json-data-using-clustered-columnstore-indexes/

Real Time Operational Analytics (HTAP)

• Real-time operational analytics: short-video why do we need it and high-level overview https://www.youtube.com/watch?v=VgV5fbh_O-0
• Business case for real-time operational analytics https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/09/real-time-operational-analytics-using-in-memory-technology/
• Using nonclustered columnstore index for real-time operational analytics https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/02/29/real-time-operational-analytics-using-nonclustered-columnstore-index/
• A simple example using NCCI: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/02/29/real-time-operational-analytics-simple-example-using-nonclustered-clustered-columnstore-index-ncci/
• How does SQL Server maintains NCCI with transactional workload https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/04/real-time-operational-analytics-dml-operations-and-nonclustered-columnstore-index-ncci-in-sql-server-2016/
• Minimizing impact of columnstore index maintenance using filtered index https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-filtered-nonclustered-columnstore-index-ncci/
• Minimizing impact of columnstore index maintenance using compression delay https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-compression-delay-option-for-nonclustered-columnstore-index-ncci/
• Minimizing impact of columnstore index maintenance using compression delay - performance numbers https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-compression-delay-option-with-ncci-and-the-performance/
• Real time operational analytics with memory-optimized tables https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/07/real-time-operational-analytics-memory-optimized-table-and-columnstore-index/
• Difference between CCI/NCCI https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/18/columnstore-index-differences-between-clusterednonclustered-columnstore-index/
• Minimizing index fragmentation in columnstore index https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/07/columnstore-index-defragmentation-using-reorganize-command/
• Columnstore index and the merge policy https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/08/columnstore-index-merge-policy-for-reorganize/

 

Thanks,

Sunil Agarwal

SQL Server Tiger Team

Twitter | LinkedIn

Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam

Comments

• Anonymous
  April 03, 2017
  Thanks for the effort