HI,
Thanks for quick reply, I have gone through the post. so what is the best way to avoid this locks. we are using nolock hint as we can have dirty read.
as this was on going problem in our production server. if i use offline indexes it will block even more.
GilaMonster (1/8/2013)
You can't. Sch-M is a schema modification lock. Blocks everything and is blocked by absolutely anything, including the Sch-S locks that all queries have to take. Online index rebuilds only require short-lived Sch-M locks, so shouldn't be a major issue.Oh, and you might want to ditch those nolock hints.... See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx