SQL Server Home
Indexed view creation causes queries on the base table to block
7/29/2010 7:42:38 AM
User(s) can reproduce this bug
While building the clustered index on an indexed view, queries against the base tables may block due to a schema stability lock. Since building the index may take many hours on big systems, this renders indexed views virtually useless for databases that have strict availability requirements. This is a major blocking issue for some of my customers.
SQL Server 2008 SP1
Windows Server 2008 R2
Operating System Language
Steps to Reproduce
Instructions: First kick off window 1 part A. After it completes kick off window 1 part B. Immediately after starting window 1 part B, kick off window 2 and prepare to wait. While waiting, check out wait stats to see the schema stability lock.
----window 1 part A----
create table t (i int not null primary key)
a as (select 1 a union all select 1),
b as (select 1 a from a a1, a a2),
c as (select 1 a from b b1, b b2),
d as (select 1 a from c c1, c c2),
e as (select 1 a from d d1, d d2),
f as (select 1 a from e e1, e e2)
insert t (i)
ROW_NUMBER() over (order by (select null))
----/window 1 part A----
----window 1 part B----
create view v
with schemabinding as
create unique clustered index ix_v on v (i)
----/window 1 part B----
where i = 10
The code in window 2 blocks on a schema until the clustered index is finished building in window 1.
The code in window 2 should not block, period! The indexed view is not being referenced by this code and there is no reason for it to be blocked by creation of said view.
to post a comment.
Please enter a comment.
on 9/7/2012 at 9:35 AM
Thank you for reporting this is already addressed. We will close this item.
Paul White NZ
on 7/12/2011 at 11:02 PM
This appears to have been fixed as of Denali CTP 3.
A shared table lock is taken on the source data, with Sch-M on the view only.
Paul White NZ
on 9/25/2010 at 1:43 AM
Can you explain why a schema *modification* lock is taken rather than schema stability plus shared table lock?
The latter combination would not block concurrent readers, only writers.
Further down the track you might consider lazy initialization of indexed views (and/or lazy maintenance) using snapshot facilities, but the change in locking behaviour above could be done today, couldn't it?
on 8/13/2010 at 5:18 PM
Thanks for reporting it. I am converting this to a DCR but it could be argued that it is a defect. We will look into this in a future release. I do realize that there are 32 or so votes for this at this time. It is clearly causing significant pains in customer domain
to post a workaround.
Please enter a workaround.
© 2014 Microsoft