Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables?

Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)

Tracing the Locks

To trace the locks, I just used one of the built-in XEvents templates tor tracing locks, added a filter so it only looked at locks from my current session (53), and set it to write to an event file. The query scripts out like this:

Creating the Indexed View

I test it out by creating and dropping the indexed view this way:

Now let’s analyze the locks!

I stop my trace and open up the event file. I click around in the magic XEvents toolbar and group by object_id and mode (that’s the lock mode). And here’s what I get:

Locks!

Locks!

The low object_ids are internal system tables. Object_id 526624919 =dbo.Posts. Object_id 843150049 = dbo.IndexMe.

Translating this out:

  • dbo.Posts required S and SCH_S locks
  • dbo.IndexMe required X, SCH_S, and SCH_M locks

We didn’t require exclusive locks on the underlying table. I did require shared locks, and creating this indexed view could require lots of IO or be part of a blocking chain with other transactions. But creating the indexed view only needed exclusive locks on the indexed view itself.

Previous Post
SQL Server 2016 CTP2.4: Maintenance Plan Changes
Next Post
How to Download the Stack Overflow Database

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}