rocks (1/8/2013)
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.
You cannot avoid schema locks. All queries absolutely must in every case take schema stability locks. An index rebuild, even an online one, needs a schema mod lock (very short lived for an online rebuild) and a schema modification lock is blocked by any other lock that exists and will in turn block any other lock request.
Dirty reads are allowed, so the users are absolutely fine with their reports potentially missing chunks of data, potentially reading sets of rows twice or more? They fully understand and accept that any results they see are potentially incorrect?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability