New load balancing features with read-only replicas<\/a><\/li>\n<\/ul>\nAfter reading some improvements from the columnstore index feature side, I was very surprised to see the following section:<\/p>\n
![\"blog_53_-_1-_cci_new_features\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_53_-_1-_cci_new_features.jpg\")
<\/p>\n
<\/p>\n
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.<\/p>\n
So let\u2019s create a clustered columnstore index on my lab environment. I will use for my tests the AdventureWorksDW2012<\/em> database and the FactProductInventory <\/em>fact table.<\/p>\nA first look at this fact table tells us that we\u2019ll 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\u2019ll able to create the clustered columnstore index.<\/p>\n
sp_helpconstraint ‘dbo.FactProductInventory’;<\/span><\/span><\/span><\/span><\/div>\ngo<\/span><\/div>\n![\"blog_53_-_2-_cci_fk_pk\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_53_-_2-_cci_fk_pk.jpg\")
<\/p>\n
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.<\/p>\n
— drop primary key<\/span><\/div>\nalter table dbo.FactProductInventory<\/span><\/span><\/span><\/span><\/div>\ndrop constraint PK_FactProductInventory;<\/span><\/span><\/span><\/span><\/div>\ngo<\/span><\/div>\n\u00a0<\/span><\/div>\n— create CCI<\/span><\/div>\ncreate clustered columnstore index idx_cci_FactProductInventory<\/span><\/span><\/span><\/span><\/span><\/div>\non FactProductInventory<\/span><\/span><\/div>\n\u00a0<\/span><\/div>\n— create unique constraint on ProductKey, DateKey columns<\/span><\/div>\nalter table dbo.FactProductInventory<\/span><\/span><\/span><\/span><\/div>\nadd constraint PK_FactProductInventory unique (ProductKey, DateKey);<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\ngo<\/span><\/div>\n <\/p>\n
Let\u2019s add 2 rows in the dbo.FactProductInventory<\/em> \u2026<\/p>\ninsert [AdventureWorksDW2012].[dbo].[FactProductInventory]<\/span><\/span><\/span><\/span><\/div>\nvalues (167, ‘20090101’, ‘2009-01-01’, 0.19, 0, 0, 875)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0<\/span><\/div>\ninsert [AdventureWorksDW2012].[dbo].[FactProductInventory]<\/span><\/span><\/span><\/span><\/div>\nvalues (167, ‘20091002’, ‘2009-01-02’, 0.19, 0, 0, 875)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n\u2026 and let\u2019s take a look at this columstore index configuration:<\/p>\n
select<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 partition_number,<\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 state_description,<\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 count(*) as nb_row_groups,<\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(total_rows) as total_rows,<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(size_in_bytes) \/ 1024 \/ 1024 as size_mb<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nfrom sys.column_store_row_groups<\/span><\/span><\/span><\/span><\/div>\nwhere object_id = object_id(‘dbo.FactProductInventory’)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\ngroup by partition_number, state_description<\/span><\/span><\/span><\/span><\/div>\norder by partition_number, state_description;<\/span><\/span><\/span><\/span><\/span><\/div>\ngo<\/span><\/div>\n![\"blog_53_-_3-_cci_rowgroups\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_53_-_3-_cci_rowgroups.jpg\")
<\/p>\n
Finally let\u2019s add this AdventureWorks2012DW<\/em> database to my availability group named 2016Grp:<\/p>\n![\"blog_53_-_4-_cci_aag\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_53_-_4-_cci_aag.jpg\")
<\/p>\n
Now, let\u2019s try to query my columnstore index on the SQL162 replica configured as secondary read-only replica:<\/p>\n
select @@servername as server_name;<\/span><\/span><\/span><\/span><\/span><\/div>\ngo<\/span><\/div>\n\u00a0<\/span><\/div>\nselect ProductKey, max(DateKey)<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\nfrom [AdventureWorksDW2012].[dbo].[FactProductInventory]<\/span><\/span><\/span><\/span><\/div>\nwhere ProductKey = 167<\/span><\/span><\/span><\/div>\ngroup by ProductKey;<\/span><\/span><\/span><\/span><\/div>\ngo<\/span><\/div>\n![\"blog_53_-_5-_cci_query\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_53_-_5-_cci_query.jpg\")
<\/p>\n
<\/p>\n
Do you remember this error message with SQL Server 2014?<\/p>\n
Msg 35371, Level 16, State 1, Line 120<\/span><\/em><\/div>\nSNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.<\/span><\/em><\/div>\nWith 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\u2019s have a look at the result of the following query:<\/p>\n
select<\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 st.session_id,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 st.is_user_transaction,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tat.transaction_id,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tat.transaction_sequence_num,<\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tat.is_snapshot<\/span><\/span><\/div>\nfrom sys.dm_tran_active_snapshot_database_transactions as tat<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\njoin sys.dm_tran_session_transactions as st<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on tat.transaction_id = st.transaction_id<\/span><\/span><\/span><\/span><\/span><\/div>\n![\"blog_53_-_6-_cci_tran\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_53_-_6-_cci_tran.jpg\")
<\/p>\n
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.<\/p>\n
But I\u2019m 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\u2019t prevent the read-only queries from taking schema stability locks and blocking DDL statements.<\/p>\n
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.<\/p>\n
Here my horrible and inefficient query that I executed from the secondary. The idea is to hold a schema stability lock during my test.<\/p>\n
— from the secondary replica<\/span><\/div>\nselect c.ProductKey, max(c.DateKey)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n