Infrastructure at your Service

David Barbarin

SQL Server 2016 : availability groups and the new clustered columnstore index support

This blog post is focused on the new supportability of clustered columnstore index on the high- availability read-only replicas.

Others studies are available here:

After reading some improvements from the columnstore index feature side, I was very surprised to see the following section:

blog_53_-_1-_cci_new_features

 

Clustered columnstore indexes are now supported on AlwaysOn readable secondary replicas! Wonderful! And I guess that the new support of both SI and RCSI transaction isolation level have something to do with this improvement.

So let’s create a clustered columnstore index on my lab environment. I will use for my tests the AdventureWorksDW2012 database and the FactProductInventory fact table.

A first look at this fact table tells us that we’ll probably face an error message because this table contains some foreign keys and one primary key that are not supported on SQL Server 2014. Fortunately, SQL Server 2016 has no such limitations and we’ll able to create the clustered columnstore index.

sp_helpconstraint ‘dbo.FactProductInventory’;
go

blog_53_-_2-_cci_fk_pk

We can notice two foreign keys and one primary key. The latter is clustered so before creating the clustered columnstore, I will have to change the primary key to a unique constraint.

– drop primary key
alter table dbo.FactProductInventory
drop constraint PK_FactProductInventory;
go
 
– create CCI
create clustered columnstore index idx_cci_FactProductInventory
on FactProductInventory
 
– create unique constraint on ProductKey, DateKey columns
alter table dbo.FactProductInventory
add constraint PK_FactProductInventory unique (ProductKey, DateKey);
go

 

Let’s add 2 rows in the dbo.FactProductInventory

insert [AdventureWorksDW2012].[dbo].[FactProductInventory]
values (167, ‘20090101’, ‘2009-01-01′, 0.19, 0, 0, 875)
 
insert [AdventureWorksDW2012].[dbo].[FactProductInventory]
values (167, ‘20091002’, ‘2009-01-02′, 0.19, 0, 0, 875)

… and let’s take a look at this columstore index configuration:

select
       partition_number,
       state_description,
       count(*) as nb_row_groups,
       sum(total_rows) as total_rows,
       sum(size_in_bytes) / 1024 / 1024 as size_mb
from sys.column_store_row_groups
where object_id = object_id(‘dbo.FactProductInventory’)
group by partition_number, state_description
order by partition_number, state_description;
go

blog_53_-_3-_cci_rowgroups

Finally let’s add this AdventureWorks2012DW database to my availability group named 2016Grp:

blog_53_-_4-_cci_aag

Now, let’s try to query my columnstore index on the SQL162 replica configured as secondary read-only replica:

select @@servername as server_name;
go
 
select ProductKey, max(DateKey)
from [AdventureWorksDW2012].[dbo].[FactProductInventory]
where ProductKey = 167
group by ProductKey;
go

blog_53_-_5-_cci_query

 

Do you remember this error message with SQL Server 2014?

Msg 35371, Level 16, State 1, Line 120
SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.

With SQL Server 2016 it seems to be another story and it appears to be working properly and this is because snapshot is now supported for clustered columnstore indexes. Let’s have a look at the result of the following query:

select
       st.session_id,
       st.is_user_transaction,
       tat.transaction_id,
       tat.transaction_sequence_num,
       tat.is_snapshot
from sys.dm_tran_active_snapshot_database_transactions as tat
join sys.dm_tran_session_transactions as st
       on tat.transaction_id = st.transaction_id

blog_53_-_6-_cci_tran

The session with id = 65 concerns my query here. We may notice that it is using snapshot without specifying anything transaction isolation level parameter from my side. As a reminder all read-only queries on a secondary replica are automatically overridden to snapshot isolation and row version mechanism to avoid blocking contention.

But I’m also curious to know if we will face the same blocked redo thread issue in this case? As you know, snapshot isolation prevents read-only queries on the secondary replicas from taking locks and preventing other DML statements against the database from executing, but it doesn’t prevent the read-only queries from taking schema stability locks and blocking DDL statements.

So I wondered if operations issued by the tuple mover in order to switch data from delta store to a row group could stuck the redo thread from executing correctly. To create such situation we may use a long running query on the columnstore index from the secondary replica and in the same time we may insert sufficient data to close a delta store from the primary replica.

Here my horrible and inefficient query that I executed from the secondary. The idea is to hold a schema stability lock during my test.

– from the secondary replica
select c.ProductKey, max(c.DateKey)
from [AdventureWorksDW2012].[dbo].[FactProductInventory] as c
cross join [AdventureWorksDW2012].[dbo].[FactProductInventory] as c2
cross join [AdventureWorksDW2012].[dbo].[FactProductInventory] as c3
group by c.ProductKey;
go

And the locks hold by my query:

blog_53_-_7-_ro_query_locks

Then I inserted sufficient data to close my delta store:

blog_53_-_8-_cci_delta_store_closed

After calling manually the tuple mover by using the ALTER INDEX REORGANIZE command, I didn’t face a blocking redo thread situation.

– from secondary replica
use AdventureWorksDW2012
go
 
select
       r.replica_server_name,
       g.name as aag_name,
       drs.synchronization_state_desc as replica_state,
       drs.synchronization_health_desc as synchro_state,
       drs.redo_queue_size
from sys.dm_hadr_database_replica_states as drs
join sys.availability_replicas as r
       on r.replica_id = drs.replica_id
join sys.availability_groups as g
       on g.group_id = drs.group_id
where g.name = ‘2016Grp’
       and drs.database_id = DB_ID();

blog_53_-_9-_cci_redo_size_queue

In fact, we can expect to this result because according to the Microsoft documentation, ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during this operation.

So the new situation is the following on both replicas:

blog_53_-_10-_cci_reorganize

Note the new delta store state TOMBSTONE here. I got some information from the Niko Neugebauer (@NikoNeugebauer) blog post here. In fact row group in TOMBSTONE state are delta stores that got compressed (row_group_id = 16 to row_group_id = 18 in my context). This unused row group will be dropped asynchronously by the garbage collector by the tuple mover.

Finally, let’s try to rebuild my columnstore index. In this case we may expect to be in a situation where the redo thread will be blocked by this DDL operation.

– from the primary replica
alter index [idx_cci_FactProductInventory]
on [dbo].[FactProductInventory]

Let’s take a look at the redo queue from my secondary replica:

– from secondary replica
use AdventureWorks2012DW
go
 
select
       r.replica_server_name,
       g.name as aag_name,
       drs.synchronization_state_desc as replica_state,
       drs.synchronization_health_desc as synchro_state,
       drs.redo_queue_size
from sys.dm_hadr_database_replica_states as drs
join sys.availability_replicas as r
       on r.replica_id = drs.replica_id
join sys.availability_groups as g
       on g.group_id = drs.group_id
where g.name = ‘2016Grp’
       and drs.database_id = DB_ID();

blog_53_-_11-_ro_blocked_redo_thread

This time, the ALTER INDEX REBUILD operation has a negative effect on the redo thread which is now stuck while the reporting query execution.

– from the secondary replica
select session_id,
       command,
       blocking_session_id,
       wait_time,
       wait_type,
       wait_resource
from sys.dm_exec_requests
where command = ‘DB STARTUP’

blog_53_-_12-_ro_blocked_redo_thread_2

The blocking session concerns my reporting query here:

 

The bottom line

Introducing the new clustered columnstore index in AlwaysOn availability groups will be definitively a good improvement in several aspects. Indeed, even if clustered columnstore indexes are designed to save a lot of resources, it is always interesting to benefit this feature to offload reporting activities in some scenarios. Moreover, it will be also a good answer to the existing lack of both SI and RCSI transaction isolation levels and the reporting data consistency without locking. I’m looking forward to see these both features in action in production environments soon!

 

Leave a Reply


× six = 36

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader