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

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 581056 Comments
Status Closed Workarounds
Type Bug Repros 9
Opened 7/29/2010 7:42:38 AM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Peter [MSFT] 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

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?

Posted by Sunil [MSFT] 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