Columnstore

I am deeply fascinated by the Columnstore Indexes, and I have open some Connect Items to suggest their important improvements:
Implement Batch Mode Support for Row Store
Multi-threaded rebuilds of Clustered Columnstore Indexes break the sequence of pre-sorted segment ordering (Order Clustering)
Columnstore Segments Maintenance – Remove & Merge
Implement Computed Columns for Clustered Columnstore Indexes

Scripts Library:
I am publishing CSIL – Columnstore Indexes Script Library, with the first release targeting the 1st of September 2015.
Sign up for notifications, if you are interested!

Here is the series of blog posts that I have written about them:

Azure:
Azure Columnstore, part 1 – The initial Preview offering
Azure Columnstore, part 2 – Snapshot Isolation & Batch Mode DOP
Azure Columnstore, part 3 – Modern Segment Elimination and Set Statistics IO

SQL Server:
Columnstore Indexes – part 1 (“Intro”)
Columnstore Indexes – part 2 (“Internals”)
Columnstore Indexes – part 3 (“More Internals”)
Columnstore Indexes – part 4 (“Basic T-SQL”)
Columnstore Indexes – part 5 (“New Meta-Information and System Stored Procedure”)
Columnstore Indexes – part 6 (“Observing the behavior”)
Columnstore Indexes – part 7 (“Transaction Isolation”)
Columnstore Indexes – part 8 (“Locking”)
Columnstore Indexes – part 9 (“CTP1 Observations”)
Columnstore Indexes – part 10 (“Compression basics”)
Columnstore Indexes – part 11 (“Clustered vs Nonclustered compression basics”)
Columnstore Indexes – part 12 (“Compression Dive”)
Columnstore Indexes – part 13 (“Dictionaries Analyzed”)
Columnstore Indexes – part 14 (“Partitioning”)
Columnstore Indexes – part 15 (“Partitioning Advanced”)
Columnstore Indexes – part 16 (“Index Builds”)
Columnstore Indexes – part 17 (“Resources 2012 vs 2014”)
Columnstore Indexes – part 18 (“Basic Batch Mode Improvements”)
Columnstore Indexes – part 19 (“Batch Mode 2012 Limitations … Updated!”)
Columnstore Indexes – part 20 (“TempDB Spills – when memory is not enough”)
Columnstore Indexes – part 21 (“DBCC CSIndex”)
Columnstore Indexes – part 22 (“Invisible Row Groups”)
Columnstore Indexes – part 23 (“Data Loading”)
Columnstore Indexes – part 24 (“Data Loading continued”)
Columnstore Indexes – part 25 (“Faster Smaller Better Stronger”)
Columnstore Indexes – part 26 (“Backup & Restore”)
Columnstore Indexes – part 27 (“Load with Delta-Stores”)
Columnstore Indexes – part 28 (“Update vs Delete + Insert”)
Columnstore Indexes – part 29 (“Data Loading for Better Segment Elimination”)
Columnstore Indexes – part 30 (“Bulk Load API Magic Number”)
Columnstore Indexes – part 31 (“Memory Pressure and Row Group Sizes”)
Columnstore Indexes – part 32 (“Size Does Matter, but how ?”)
Columnstore Indexes – part 33 (“A Tuple Mover that closes open Delta-Stores”)
Columnstore Indexes – part 34 (“Deleted Segments Elimination”)
Columnstore Indexes – part 35 (“Trace Flags & Query Optimiser Rules”)
Columnstore Indexes – part 36 (“Maintenance Solutions for Columnstore”)
Columnstore Indexes – part 37 (“Deleted Bitmap & Delta-Store Sizes”)
Columnstore Indexes – part 38 (“Memory Structures”)
Columnstore Indexes – part 39 (“Memory in Action”)
Columnstore Indexes – part 40 (“Compression Algorithms”)
Columnstore Indexes – part 41 (“Statistics”)
Columnstore Indexes – part 42 (“Materialisation”)
Columnstore Indexes – part 43 (“Transaction Log Basics”)
Columnstore Indexes – part 44 (“Monitoring with Extended Events”)
Columnstore Indexes – part 45 (“Multi-Dimensional Clustering”)
Columnstore Indexes – part 46 (“DateTime compression and performance”)
Columnstore Indexes – part 47 (“Practical Monitoring with Extended Events”)
Columnstore Indexes – part 48 (“Improving Dictionary Pressure”)
Columnstore Indexes – part 49 (“Data Types & Predicate Pushdown”)
Columnstore Indexes – part 50 (“Columnstore IO”)
Columnstore Indexes – part 51 (“SSIS, DataFlow & Max Buffer Memory”)
Columnstore Indexes – part 52 (“What’s new for Columnstore XE in SQL Server 2014 SP1”)
Columnstore Indexes – part 53 (“What’s new for Columnstore in SQL Server 2014 SP1”)
Columnstore Indexes – part 54 (“Thoughts on upcoming improvements in SQL Server 2016″)
Columnstore Indexes – part 55 (“New Architecture Elements in SQL Server 2016”)
Columnstore Indexes – part 56 (“New DMV’s in SQL Server 2016”)
Columnstore Indexes – part 57 (“Segment Alignment Maintenance”)
Columnstore Indexes – part 58 (“String Predicate Pushdown”)
Columnstore Indexes – part 59 (“Aggregate Pushdown”)
Columnstore Indexes – part 60 (“3 More Batch Mode Improvements in SQL Server 2016”)
Columnstore Indexes – part 61 (“Window aggregate functions”)
Columnstore Indexes – part 62 (“Row Groups Trimming”)
Columnstore Indexes – part 63 (“Parallel Data Insertion”)
Columnstore Indexes – part 64 (“T-SQL Improvements in SQL Server 2016”)
Columnstore Indexes – part 65 (“Clustered Columnstore Improvements in SQL Server 2016”)
Columnstore Indexes – part 66 (“More Clustered Columnstore Improvements in SQL Server 2016”)
Columnstore Indexes – part 67 (“Clustered Columstore Isolation Levels & Transactional Locking”)
Columnstore Indexes – part 68 (“Data Loading, Delta-Stores & Vertipaq Compression Optimisation”)
Columnstore Indexes – part 69 (“Operational Analytics – Rowstore”)
Columnstore Indexes – part 70 (“Filtered Indexes in Action”)
Columnstore Indexes – part 71 (“Change Data Capture, Change Tracking & Temporal”)
Columnstore Indexes – part 72 (“InMemory Operational Analytics”)
Columnstore Indexes – part 73 (“Big Delta-Stores with Nonclustered Columnstore”)
Columnstore Indexes – part 74 (“Row Group Merging & Cleanup, SQL Server 2016 edition”)
Columnstore Indexes – part 75 (“Stretch DB & Columnstore”)
Columnstore Indexes – part 76 (“Compression Delay”)
Columnstore Indexes – part 77 (“SSIS 2016 & Columnstore”)
Columnstore Indexes – part 78 (“Temporary Objects”)
Columnstore Indexes – part 79 (“Loading Data into Non-Updatable Nonclustered Columnstore”)
Columnstore Indexes – part 80 (“Local Aggregation”)
Columnstore Indexes – part 81 (“Adding Columnstore Index to InMemory Tables”)
Columnstore Indexes – part 82 (“Extended Events in SQL Server 2016”)
Columnstore Indexes – part 83 (“Columnstore Replication in SQL Server 2016”)
Columnstore Indexes – part 84 (“Practical Dictionary Cases”)
Columnstore Indexes – part 85 (“Important Batch Mode Changes in SQL Server 2016”)
Columnstore Indexes – part 86 (“New Trace Flags in SQL Server 2016”)
Columnstore Indexes – part 87 (“Indexed Views”)
Columnstore Indexes – part 88 (“Minimal Logging in SQL Server 2016”)
Columnstore Indexes – part 89 (“Memory-Optimised Columnstore Limitations 2016”)
Columnstore Indexes – part 90 (“In-Memory Columnstore Improvements in Service Pack 1 of SQL Server 2016 “)
Columnstore Indexes – part 91 (“SQL Server 2016 Standard Edition Limitations”)
Columnstore Indexes – part 92 (“Lobs”)
Columnstore Indexes – part 93 (“Batch Mode Adaptive Memory Grant Feedback”)
Columnstore Indexes – part 94 (“Use Partitioning Wisely”)
Columnstore Indexes – part 95 (“Basic Query Patterns”)
Columnstore Indexes – part 96 (“Nonclustered Columnstore Index Online Rebuild”)
Columnstore Indexes – part 97 (“Working with Strings”)
Columnstore Indexes – part 98 (“Null Expressions & String Aggregates”)
Columnstore Indexes – part 99 (“Merge”)
Columnstore Indexes – part 100 (“Identity”)
Columnstore Indexes – part 101 (“Estimated? Similar! Similar How?”)
Columnstore Indexes – part 102 (“CCI with Secondary Rowstore Indexes on SQL 2014”)
Columnstore Indexes – part 103 (“Partitioning 2016 vs Partitioning 2014”)
Columnstore Indexes – part 104 (“Batch Mode Adaptive Joins”)
Columnstore Indexes – part 105 (“Performance Counters”)
Columnstore Indexes – part 106 (“Memory Requirements for Rebuild & Reorganize”)
Columnstore Indexes – part 107 (“Dictionaries Deeper Dive”)

8 thoughts on “Columnstore

  1. Anuj Saboo

    Hello,

    I have heard praise about your blog from Brent Ozar podcasts and I would want to ask you a question about ColumnStore Indexes as a DBA. I use SQL 2014 and using the traditional DMV – sys.dm_db_index_physical_stats, I am not able to find fragmentation on Clustered Columnstore Index. When I manually try to find the fragmentation by going into Index Properties, the fragmentation shows at 0% which is quite surprising seeing that I do a lot of data inserts/deletes in my Data Warehouse.

    Does the fragmentation work in some other way, is there any other method to see fragmentation on ColumnStore Indexes?

    1. Niko Neugebauer Post author

      Hi Anuj,

      Columnstore Indexes do not have physical fragmentation in the same sense as the traditional Rowstore indexes. The columnstore segments are stored as LOBs continuously.
      You have the logical fragmentation, because of the deleted rows. For more information check out these posts:
      http://www.nikoport.com/2014/07/29/clustered-columnstore-indexes-part-36-maintenance-solutions-for-columnstore/
      http://www.nikoport.com/2015/06/28/columnstore-indexes-part-57-segment-alignment-maintenance/
      http://www.nikoport.com/2014/07/20/clustered-columnstore-indexes-part-34-deleted-segments-elimination/

      Additionally check out the following script at the CISL library (SQL Server 2016 version):
      https://github.com/NikoNeugebauer/CISL/blob/master/SQL-2016/fragmentation.sql

      Best regards,
      Niko

Leave a Reply

Your email address will not be published. Required fields are marked *