Clustered Column Store Index: Concurrency with INSERT Operations

Published Mar 23 2019 01:28 PM 190 Views
Microsoft
First published on MSDN on Jul 27, 2014

Clustered Column Store: Insert Operations


As described in the blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-... , the clustered column store index has been optimized for typical DW scenario supporting nightly or trickle data load with fast query performance. Multiple inserts can load the data in parallel concurrently while DW queries are being run in read uncommitted transaction isolation level.


This blog describes locking behavior when data is inserted concurrently. For the scenarios below, we will use the following table


CREATE TABLE [dbo].[T_ACCOUNT](


[accountkey] [int] IDENTITY(1,1) NOT NULL,


[accountdescription] [nvarchar](50) NULL


) ON [PRIMARY]



-- create a CCI


CREATE CLUSTERED COLUMNSTORE  INDEX ACCOUNT_CI ON T_ACCOUNT



Insert Operations


Let us insert 1 row and see the locks taken. Note, we did not commit the transaction


begin tran


insert into T_ACCOUNT (accountdescription ) values ('row-1');


Here are the locks. Note, the new row is inserted into delta rowgroup which is organized as a btree in traditional row storage format. There is a new resource ROWGROUP in the context of CCI. The current transaction has taken IX lock on the ROWGROUP



Now, let us insert another row in another session as follows and look at the lock


begin tran


insert into T_ACCOUNT (accountdescription ) values ('row-2');


Note, that the second transaction in session-55, also inserted the row into the same rowgroup. In other words, concurrent inserts can load the data into same rowgroup without blocking each other.



In summary, the insert into CCI does not block other concurrent inserts and concurrent inserts load data into the same delta rowgorup. In the next blog, we will look into BulkLoad Operations


Thanks


Sunil Agarwal


%3CLINGO-SUB%20id%3D%22lingo-sub-384337%22%20slang%3D%22en-US%22%3EClustered%20Column%20Store%20Index%3A%20Concurrency%20with%20INSERT%20Operations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384337%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2027%2C%202014%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1680720616%22%20id%3D%22toc-hId--439254280%22%3EClustered%20Column%20Store%3A%20Insert%20Operations%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EAs%20described%20in%20the%20blog%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlserverstorageengine%2Farchive%2F2014%2F07%2F27%2Fclustered-column-store-index-concurrency-and-isolation-level.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlserverstorageengine%2Farchive%2F2014%2F07%2F27%2Fclustered-column-store-index-concurrency-and-isolation-level.aspx%20%3C%2FA%3E%20%2C%20the%20clustered%20column%20store%20index%20has%20been%20optimized%20for%20typical%20DW%20scenario%20supporting%20nightly%20or%20trickle%20data%20load%20with%20fast%20query%20performance.%20Multiple%20inserts%20can%20load%20the%20data%20in%20parallel%20concurrently%20while%20DW%20queries%20are%20being%20run%20in%20read%20uncommitted%20transaction%20isolation%20level.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20blog%20describes%20locking%20behavior%20when%20data%20is%20inserted%20concurrently.%20For%20the%20scenarios%20below%2C%20we%20will%20use%20the%20following%20table%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20%5Bdbo%5D.%5BT_ACCOUNT%5D(%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Baccountkey%5D%20%5Bint%5D%20IDENTITY(1%2C1)%20NOT%20NULL%2C%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5Baccountdescription%5D%20%5Bnvarchar%5D(50)%20NULL%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E)%20ON%20%5BPRIMARY%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20create%20a%20CCI%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECREATE%20CLUSTERED%20COLUMNSTORE%20%26nbsp%3BINDEX%20ACCOUNT_CI%20ON%20T_ACCOUNT%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--871436345%22%20id%3D%22toc-hId-2048258553%22%3EInsert%20Operations%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3ELet%20us%20insert%201%20row%20and%20see%20the%20locks%20taken.%20Note%2C%20we%20did%20not%20commit%20the%20transaction%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%20tran%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einsert%20into%20T_ACCOUNT%20(accountdescription%20)%20values%20('row-1')%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%20are%20the%20locks.%20Note%2C%20the%20new%20row%20is%20inserted%20into%20delta%20rowgroup%20which%20is%20organized%20as%20a%20btree%20in%20traditional%20row%20storage%20format.%20There%20is%20a%20new%20resource%20ROWGROUP%20in%20the%20context%20of%20CCI.%20The%20current%20transaction%20has%20taken%20IX%20lock%20on%20the%20ROWGROUP%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97772i865788AEFA8DBA39%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENow%2C%20let%20us%20insert%20another%20row%20in%20another%20session%20as%20follows%20and%20look%20at%20the%20lock%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ebegin%20tran%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einsert%20into%20T_ACCOUNT%20(accountdescription%20)%20values%20('row-2')%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENote%2C%20that%20the%20second%20transaction%20in%20session-55%2C%20also%20inserted%20the%20row%20into%20the%20same%20rowgroup.%20In%20other%20words%2C%20concurrent%20inserts%20can%20load%20the%20data%20into%20same%20rowgroup%20without%20blocking%20each%20other.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97773iDAC5B06A086B6D81%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20summary%2C%20the%20insert%20into%20CCI%20does%20not%20block%20other%20concurrent%20inserts%20and%20concurrent%20inserts%20load%20data%20into%20the%20same%20delta%20rowgorup.%20In%20the%20next%20blog%2C%20we%20will%20look%20into%20BulkLoad%20Operations%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThanks%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESunil%20Agarwal%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384337%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2027%2C%202014%20Clustered%20Column%20Store%3A%20Insert%20OperationsAs%20described%20in%20the%20blog%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fblogs%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fblogs%3C%2FA%3E.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384337%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerStorageEngine%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 01:28 PM
Updated by: