Performance Tuning

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

By Kendra Little · October 1, 2015 · no comments

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 (
     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

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:

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

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.

Free, 3× a week

Get my new posts by email

Three posts a week, plus a Monday roundup of the best database news from around the web.

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

Tracing the Locks

Creating the Indexed View

Now let’s analyze the locks!

Get my new posts by email

Keep digging

Leave a comment Cancel reply