Azure SQL Database Online Columnstore Unicorn and Rainbow Theory

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 )

running

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

iamblocked

The famous LCK_M_S wait type exists.

lckms

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

lockissue

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s