Unexpected Blocking during the Indexed View Creation

The problem 🔗︎

There was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.

From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.

Because the index might be large and the maintenance window small, I want to do that as fast as possible.

Research 🔗︎

I’ve found several blog posts already tackling this topic in my research.

The first one is from Michael J Swart (t) - How to Create Indexed Views Online.

Michael introduces a helper column IsMigrated that helps create an empty Indexed View instantly and then batch out the data load. The downside is that the column remains there.

It’s an interesting approach, but since the article is seven years old (at the time of writing this blog post), I was wondering if there has been any improvement since.

Next, I ran across this SO answer from Paul White (t|b) - Why does an index rebuild requires a Sch-M lock?

Some time ago, the Sch-M restriction was applied when creating an indexed view. That was pointed out to be unnecessary (Connect link no longer available) because no structure was being dropped, only created, so the behaviour was changed (to only take Sch-S and Tab-S).

This answer is from some three years ago.

This is also supported by the blog post from Kendra Little (t|b) - Does Creating an Indexed View Require Exclusive Locks on an Underlying Table? from the same year.

Kendra’s post contains a demo that proves that creating a Clustered index on the View only needs SCH-M locks on the View itself.

But I was still blocked 🔗︎

The SQL Server told you to reject the evidence of your eyes and ears.
— George Orwell (probably)

I had all the needed proofs, but not the expected result. So I’ve decided to conduct the experiments myself. I’m using SQL Server 2019 Enterprise edition (the edition is essential).

I’ll reuse scripts from my previous blog post on Indexed views - IS Lock in RCSI Enabled Database to reproduce the problem.

I’ll create the Database TestLock and tables MainTable, UnrelatedTable and the view IndexedView, but let’s not create the Clustered index CX_IndexedView just yet.

We can get all the object Ids with this query:

SELECT
    o.name AS ObjectName
    , o.object_id AS ObjectId
FROM sys.objects AS o
WHERE
    o.is_ms_shipped = 0
    AND o.type IN ('U ', 'V ')

For me, the Ids are:

Let’s open two new sessions.

• In session 1, we’ll start a transaction and the Clustered index creation.
• In session 2, we will read from the UnrelatedTable

-- Session 1
BEGIN TRANSACTION

CREATE UNIQUE CLUSTERED INDEX CX_IndexedView ON dbo.IndexedView (Id)

-- Session 2
BEGIN TRANSACTION
SELECT
    RandomColumn
FROM dbo.UnrelatedTable
WHERE Id > (SELECT 0) /* avoiding a Trivial plan */

Session 2 should be blocked. Let’s note the Ids of both sessions and plug them along with the object Ids into this script.

SELECT DISTINCT
    dtl.request_session_id
    , dtl.request_mode
    , dtl.resource_associated_entity_id
FROM sys.dm_tran_locks AS dtl
WHERE
    dtl.request_session_id IN (Session1, Session2) /* add your session Ids */
    AND dtl.request_mode LIKE 'Sch-%'
    AND resource_associated_entity_id IN
    (   /* use your objectIds */
           -- MainTable
        ,  -- UnrelatedTable
        ,  -- IndexedView
    )
ORDER BY
    dtl.request_session_id
    , resource_associated_entity_id

We can see that session 2 is attempting to read from the Indexed View while it’s being created.

This is due to the feature called indexed view matching

If a query contains references to columns that are present both in an indexed view and base tables, and the Query Optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view.

Which is automatically attempted only in the Enterprise version (or Developer, which has the same programming surface).

On the one hand, usually, this helps me; on the other hand, I’m paying more money for an offline operation.

This behaviour is also confirmed by Paul White, who has looked under the hood:

I confirmed the blocking happens when the query processor goes to load dependent views, anticipating that view matching might be tried later. When EXPAND VIEWS is hinted, that step is skipped, so no blocking.

There’s no neat way to prevent automatic indexed view matching without that hint on Enterprise Edition, at least without a number of hairy side-effects.

To test this, we can rerun the blocked query, but this time with the hint EXPAND VIEWS.

SELECT
    RandomColumn
FROM dbo.UnrelatedTable
WHERE Id > (SELECT 0)
OPTION (EXPAND VIEWS)

Which is not blocked. You can rollback the transactions now and stop the blocking.

Finally, I wasn’t the only one to come across this problem.

I’m hoping this problem can be fixed in a future CU, and we won’t have to wait for a new SQL Server version.
Thank you for reading.