Clustered Columnstore Indexes – part 52 (“What’s new for Columnstore XE in SQL Server 2014 SP1”)

Continuation from the previous 51 parts, the whole series can be found at https://www.nikoport.com/columnstore/

Microsoft has launched Service Pack 1 for SQL Server over a week ago (15th of April was the release date I believe), and unfortunately the original release contained a bug related to SSISDB Database, while upgrading it would not execute the last SQL script. I was one of the first “lucky” ones to download and to face this error on my test VM.
I have managed to solve it the same night and since on the next day Microsoft has pulled away the Service Pack 1 for SQL Server until solving the issue, I believe to be one of the not too many people with a functioning version of it.
At the blogpost announcing the Service Pack there was a mention of one of the improvement for the Columnstore Indexes – a new Extended Event to be precisem and so this blog post is about my research that was made on the SQL Server with the version 12.0.4500.0

After getting my SQL Server instance to run, I have executed the following query for identifying the Extended Events that might be of interest for the type of research that I am doing:

SELECT p.name AS package_name,
	        o.name AS source_name,
	        o.description
	FROM sys.dm_xe_packages AS p
	JOIN sys.dm_xe_objects AS o 
	     ON p.guid = o.package_guid
	WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
	   AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
	   AND o.object_type = 'event' 
	   and
	   (
	      p.description like '%column_store%' or p.description like '%columnstore%' or
		  o.description like '%column_store%' or o.description like '%columnstore%' or
		  o.name like '%column_store%' or o.name like '%columnstore%'   
	   )
	   and o.name not in ('sql_batch_completed','sql_batch_starting','sql_statement_recompile','plan_guide_successful','plan_guide_unsuccessful','uncached_sql_batch_statistics','expression_compile_stop_batch_processing')
	   and p.name <> 'XtpEngine';

Here is the event that is new to the SQL Server 2014 engine:
column_store_acquire_insert_lock.

Notice that one of the obvious bugs in this version is the description for the old (since SQL Server 2012) event window_spool_ondisk_warning – “The ID of the column store segment that has been skipped during scan.” This makes no sense and I hope that the updated release of the Service Pack 1 for SQL Server 2014 will correct the description.

This new column_store_acquire_insert_lock event appeared to be especially interesting, since some of the late Cumulative Updates were focusing on the locking & deadlocking problems with Columnstore Indexes.

I decided to create a new Extended Events session with this new event:

CREATE EVENT SESSION [CCI_2014sp1] ON SERVER 
ADD EVENT sqlserver.column_store_acquire_insert_lock(
    ACTION(sqlserver.sql_text))
WITH (EVENT_RETENTION_MODE=NO_EVENT_LOSS);

I decided to create a simple table with 3 columns plus Clustered Columnstore Index and start inserting data, discovering what this new Extended Event brings:

IF OBJECT_ID('dbo.CCTest', 'U') IS NOT NULL
	drop table dbo.CCTest;

create table dbo.CCTest(
	id int not null,
	name varchar(50) not null,
	lastname varchar(50) not null );
GO

create clustered columnstore index CCL_CCTest
	on dbo.CCTest;
GO

Now I started the pre-configured Extended Events session CCI_2014sp1 and started punching in some rows:

	insert into dbo.CCTest
		( id, name, lastname )
		values
		( 1, 'SomeName_', 'SomeLastName_' );

As we can see, I have instantly caught this new event – column_store_acquire_insert_lock, the more interesting part naturally is to investigate its properties, as shown on the right side.
The properties window exposes the following properties that are definitely very useful for the debugging of the Delta-Stores behaviour:
database_id – id of the respective database;
delta_store_id – id of the involved Delta-Store;
delta_store_isnew – shows if the new Delta-Store was created for completion of the current information (insert or update). This property contains value of true and false and is very useful for the monitoring as I can imagine, putting a filter=true on this property will help to discover whenever we are opening a new Delta-Store or checking wether the inserts are hitting an existing Delta-Store for example;
lock_attempt – shows the number of attempts that were made in order to insert/update the information in the Delta-Store
lock_result – shows the result of the locking attempts (OK is the only result I have managed to get so far, I imagine something like FAIL for the cases of concurrent inserts that the SQL Server can’t solve)
lock_timeout – the timeout for the locking operation, should this value have something different from zero, this means we are getting into the locking troubles.

Let’s insert one more row:

	insert into dbo.CCTest
		( id, name, lastname )
		values
		( 1, 'SomeName_', 'SomeLastName_' );

Catching! We have got another event, let’s take a good look at its properties now.
the only visible difference lies in the delta_store_isnew property, which has a value of FALSE, since we are inserting data into an already existing Delta-Store.

Be careful whenever you start using this Extended Event, since it is being fired for each Delta-Store Insertion, you will end up with a very significant amount of information passing through and the best way to use it is only for the study of Columnstore or for some advanced debugging.

A special note at this point goes for the Bulk Inserts, which are delivered into the “Hidden” Delta-Store, becoming compressed Row Group automatically, without any direct involvement of the normal Delta-Stores. Because of that the column_store_acquire_insert_lock_properties does not fire whenever inserting new rows.

I have executed the following test on my favourite free sample Database – ContosoRetailDW:

EXEC xp_cmdshell 'bcp "SELECT top 3000000 * FROM [ContosoRetailDW].dbo.FactOnlineSales order by OnlineSalesKey" queryout "C:\Install\FactOnlineSales.rpt" -T -c -t,'; 

CREATE TABLE [dbo].[FactOnlineSales_Bulk_CCI](
    [OnlineSalesKey] [int] NOT NULL,
    [DateKey] [datetime] NOT NULL,
    [StoreKey] [int] NOT NULL,
    [ProductKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [int] NULL,
    [SalesQuantity] [int] NOT NULL,
    [SalesAmount] [money] NOT NULL,
    [ReturnQuantity] [int] NOT NULL,
    [ReturnAmount] [money] NULL,
    [DiscountQuantity] [int] NULL,
    [DiscountAmount] [money] NULL,
    [TotalCost] [money] NOT NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
);
 
create Clustered Columnstore Index PK_FactOnlineSales_Bulk_CCI
    on dbo.FactOnlineSales_Bulk_CCI
 
 
-- Import Bulk Data
BULK INSERT dbo.FactOnlineSales_Bulk_CCI
   FROM 'C:\Install\FactOnlineSales.rpt'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'
      );

As a result there were nothing new generated with the usage of the column_store_acquire_insert_lock extended event.

This is a small but nice improvement for the Columnstore Indexes monitoring in Service Pack 1, I hope that it won’t get removed in the final release, anyway this post will get updated after the updated release of the Service Pack 1 for SQL Server 2014.

to be continued with Clustered Columnstore Indexes – part 53 (“What’s new for Columnstore in SQL Server 2014 SP1”)