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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE EVENT SESSION [LockTrace] ON SERVER ADD EVENT sqlserver.lock_acquired ( SET collect_database_name=(1),collect_resource_description=(1) ACTION(sqlserver.query_hash) WHERE ( [package0].[greater_than_uint64]([database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[equal_uint64]([sqlserver].[session_id],(53)) ) ) 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) GO |
Creating the Indexed View
I test it out by creating and dropping the indexed view this way:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE VIEW dbo.IndexMe WITH SCHEMABINDING AS SELECT PostTypeId, COUNT_BIG(*) as CT FROM dbo.Posts GROUP BY PostTypeId; GO -- This is where it becomes an indexed view. CREATE UNIQUE CLUSTERED INDEX ix_indexme on dbo.IndexMe (PostTypeId) GO DROP INDEX ix_indexme on dbo.IndexMe; GO |
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.
