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:
CREATE EVENT SESSION [LockTrace] ON SERVER
ADD EVENT sqlserver.lock_acquired (
ADD TARGET package0.event_file (SET filename = N'S:\Xevents\Traces\LockTrace.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)
Creating the Indexed View
I test it out by creating and dropping the indexed view this way:
CREATE VIEW dbo.IndexMe
COUNT_BIG(*) as CT
GROUP BY PostTypeId;
-- This is where it becomes an indexed view.
CREATE UNIQUE CLUSTERED INDEX ix_indexme on dbo.IndexMe (PostTypeId)
DROP INDEX ix_indexme on dbo.IndexMe;
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:
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.