Clustered Columnstore Indexes – part 21 (“DBCC CSIndex”)

Continuation from the previous 20 parts, starting from http://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

There is a new kid on the block for Columnstore Indexes (Clustered & Nonclustered), which allow us to take a deep look into the internal stuff of the Columnstore Indexes. This one is a kind of newer brother to the good old DBCC Page function, which a lot of SQL Server professionals try to use at one occasion or another.
One absolutely awesome detail about it – is that it exists since SQL Server 2012 actually. Yes, that means that you can actually run this command on the good old Nonclustered Columnstore Indexes on SQL Server 2012. 🙂

I would be glad to claim the authorship of this discovery, but in the reality it was the awesome Dmitri Pilugin that already wrote about it a lot of time ago.

So the name of this command is simply – DBCC CSIndex and here is the known syntax for this command:

I am keep on playing with Contoso BI database while using SQL Server 2014 CTP 2.

To start I am running the necessary queries to create the Clustered Columnstore Index on the dbo.FactOnlineSales table.

Now let us get the necessary data to make the DBCC CSIndex command running:

We are ready, lets run the command:

Here comes the beginning of the output that I have executed on my table (note that your output might be quite different):
Segment Attributes:

First of all we really see all the data that we can already find in the DMVS, such as Version, Base_Id, MinDataId, MaxDataId, etc.
But in the 2nd paragraph the really interesting stuff is appearing:

Lob type – It looks that Microsoft is using different type of LOBs when storing the data
RLE – Run Length Encoding, the encoding that is being used to create the compression
Bitpack Array Index – Bitpack algorithm, used for compression.
… and so on and so forth.
I just hope that there will be someone from the product team that will explain all the output of this function soon, but realistically saying I am not expecting it to happen really soon.

Feel free to try and explore this function, but please do it only on the test VMs, since you will easily manage to produce a good amount of errors.

to be continued with Clustered Columnstore Indexes – part 22 (“Invisible Row Groups”)

Leave a Reply

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