As Microsoft states “online clustered columnstore index build enables you to optimize and compress your data with minimal downtime without major blocking operations on the queries that are executing while you are transforming the data.”

Based on one of my favourite blog posts ever about unicorns, rainbows and online index operations (https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-830-unicorns-rainbows-and-online-index-operations/) I wanted to show that using a command such as

CREATE CLUSTERED  COLUMNSTORE INDEX cciSales ON [SalesLT].[ProductModelProductDescription] WITH ( ONLINE = ON )

that some blocking may still take place when you have certain workloads running at certain times as the index build or to be more specific, if certain queries are executing first under certain conditions.

So this is how blocking can still occur with ONLINE = ON.

Window 1 – an open update transaction.

 BEGIN TRAN
UPDATE [SalesLT].[ProductModelProductDescription]
SET ModifiedDate = GETDATE()

Window 2 – my online clustered columnstore code.

 CREATE CLUSTERED  COLUMNSTORE INDEX cciSales
ON [SalesLT].[ProductModelProductDescription] WITH ( ONLINE = ON )

Here is the CREATE INDEX spid 143 is being blocked by 139, my open transaction.

The famous LCK_M_S wait type exists.

There is obviously lock incompatibility happening here.

SELECT resource_type as type, request_status as status,
       request_mode as mode, request_session_id as spid,
    resource_description as resource, resource_database_id as dbid
FROM sys.dm_tran_locks
WHERE resource_database_id = 5

Soon as I rollback the open transaction the blocking stops. So even with ONLINE operation blocking may occur if you start  certain workloads before you issue the ONLINE code.