Clustered Columnstore Indexes – part 5 (“New Meta-Information and System Stored Procedure”)

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

Now it is time to take a detailed look of the upcoming and less documented attractions related to Clustered Columnstore Indexes:

Important Note: I am referring to the CTP1 of the SQL Server 2014, which means that any functionality might be changed before the RTM without further advice.

Stored Procedures:
I have found 2 new Stored Procedures inside of the master database which are definitely related to the Columnstore Indexes – [sp_db_enable_clustered_columnstores] & [sp_cci_tuple_mover]. Judging by the name of the first Stored Procedure, Microsoft might be introducing a functionality to enable or disable Clustered Columnstore Indexes usage in a given database.
This procedure takes two parameters: one is the name of the database we want to affect, and the second corresponds to the change of the status:

alter procedure [sys].[sp_db_enable_clustered_columnstores]
(
	@dbname sysname = null,
	@value varchar(6) = null
)
...

After consulting its content, seeing and understanding its parameters usage, I decided to experiment with them on my existing databases where I have been playing with Columnstore Indexes, and so I tried to disable its usage on my ‘Columnstore’ database:

exec [sys].[sp_db_enable_clustered_columnstores] 
       @dbname = 'Columnstore', @value = 'off';

The result was quite obvious:
Table ‘dbo’.’CCTest’ has a clustered columnstore index.
Table ‘dbo’.’MaxDataTable’ has a clustered columnstore index.
Msg 35366, Level 16, State 12, Procedure sp_db_enable_clustered_columnstores, Line 39
Cannot disable clustered columnstore index support in database ‘Columnstore’ because it contains one or more clustered columnstore indexes. Consider dropping these indexes or creating clustered B-tree indexes instead, and trying again.
Indeed I have 2 tables which are using Clustered Columnstore Indexes, and so I decided to delete those tables and to try to recreate them after disabling Clustered Columnstore Indexes usage by executing that system stored procedure.

The results are definitely pointing that the work is still in progress:
The clustered_columnstores setting changed from 0 to 0.
Any further experiments with the available parameters just proven that no visible functionality changes, since I was still able to create Clustered Columnstore Indexes. This is a CTP1 and so I am really confident that things should improve already in CTP2.

Regarding the second Stored Procedure – [sp_cci_tuple_mover], this one is really a very interesting beast. Its name is suggesting that it is invoking the Tuple Mover, and if you look at its code –

CREATE PROC [sys].[sp_cci_tuple_mover] (@rowset_id BIGINT, @rowGroupId BIGINT, @rowGroupState BIGINT) AS
BEGIN
    declare @count int
    declare @dbid int
    set @dbid = db_id ()
    select @count = count (*) from OpenRowset (table COLUMNSTORE_TUPLE_MOVER, @dbid, @rowset_id, @rowGroupId, @rowGroupState)
    return @count
END			

, you will notice that there are a lot of very intriguing things: first of all this stored procedure is working directly with rowset_ids (you can find any related information from the DM_DB_DATABASE_PAGE_ALLOCATIONS dmv, if you check up on one of the tables with a Clustered Columnstore Index.
Secondly, it suggests that we actually could directly invoke it for a given Row Group and even specify a status for let’s believe setting the status of the given Row Group.
The code inside of that Stored Procedure is using a OpenRowset statement, which is mostly being used for remote connections, such as Linked Servers or Bulk Imports. In this case the parameters are quite different from the given for the SQL Server 2012 for example, and so it looks like the Columnstore_Tuple_Mover is being used as a new type of provider/operation by itself for the SQL Server 2014.
I have tried to invoke this Stored Procedure directly but at the moment for CTP1 it seems that any of the parameters are rejected with a message of an incorrect syntax, which leads to the idea that this functionality is not implemented completely yet.

My experiments so far have not determined any active usage of it, but I expect further developments in the upcoming CTPs and releases.

Dynamic Management Views:
Besides 2 already existing DMV’s for the Columnstore Indexes (sys.column_store_dictionaries & sys.column_store_segments), in CTP1 of SQL Server 2014 there is one more DMV – sys.column_store_row_groups. This view is presenting information about the existing Row Groups (Segments & Delta Stores) relative to Clustered Columnstore Indexes. It is interesting to notice that Delta Stores do not present size information – I understand that they are completely different from the functioning size, but from the storage point of view they are very same 8K pages and Extends. I expect that Microsoft shall improve this information in the future CTPs. According to the official documentation size_in_bytes should contain information for all Row Groups, so we have really good chances to have this one presenting correct information. :)
Here is the link to the official Microsoft documentation on this DMV.

Updated on the 16th of July of 2013 with the information about the [sp_cci_tuple_mover] system Stored Procedure.

to be continued with Clustered Columnstore Indexes – part 6 (“Observing the behavior”)