Indexed view creation causes queries on the base table to block

Indexed view creation causes queries on the base table to block by Adam Machanic

Closed

as Fixed

Type:	Bug
ID:	581056
Opened:	7/29/2010 7:42:38 AM
Access Restriction:	Public

Workaround(s)

view

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.

Details (expand)

English

SQL Server 2008 SP1

SQL Engine

Windows Server 2008 R2

US English

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----
use tempdb
go

create table t (i int not null primary key)
go

;with
    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)
select top(20000000)
    ROW_NUMBER() over (order by (select null))
from f
go
----/window 1 part A----

----window 1 part B----
create view v
with schemabinding as
select i
from dbo.t
go

create unique clustered index ix_v on v (i)
go
----/window 1 part B----

----window 2----
use tempdb
go

select *
from t
where i = 10
go
----/window 2----

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.

X64

0 attachments

Posted by Microsoft on 9/7/2012 at 9:35 AM

Thank you for reporting this is already addressed. We will close this item.

Posted by 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.

Posted by Paul White NZ on 9/25/2010 at 1:43 AM

Sunil,

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?

Paul

Posted by Microsoft 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

thanks
Sunil