locks caused by Sch-S

I have SQL Server 2005 EE build 3186. And on the server several times
occurred errors caused by queries timeouts. When I look at locks, I see what
the timeouts are caused by reindexation job, which rebuilding indices. The
job is blocked by other connection, which hold Sch-S lock on one of tables.
The other connection belongs to one application which call one of stored
procedures. The stored procedure only do select from several tables, not
modify any data and not start any transactions.
sp_who2 shows what that application send last batch about 7 hours before
start of reindexation job. So the lock was bheld for 7 hours.
May someone tell, is it known problem? How it may be fixed or prevented?Hi
Do not run reindex during the work hours. Moreover, examime only heavy
fragmented tables/indexs abd defragment only them.
Use alter index ... with option ONLINE (for more details see BOL)
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:EE853477-3366-4D83-B999-D86A7DB7C563@.microsoft.com...
>I have SQL Server 2005 EE build 3186. And on the server several times
> occurred errors caused by queries timeouts. When I look at locks, I see
> what
> the timeouts are caused by reindexation job, which rebuilding indices. The
> job is blocked by other connection, which hold Sch-S lock on one of
> tables.
> The other connection belongs to one application which call one of stored
> procedures. The stored procedure only do select from several tables, not
> modify any data and not start any transactions.
> sp_who2 shows what that application send last batch about 7 hours before
> start of reindexation job. So the lock was bheld for 7 hours.
> May someone tell, is it known problem? How it may be fixed or prevented?|||Reindex run not during work hours - between time when that query which caused
Sch-S lock was executed and reindexation was started, was 7 hours - query
place Sch-S lock at 9:30, and reindexation started at 16:40. And the
resulting locks was found several hours lately. Alter index, even with online
option, still is not fully online - it need to get Sch-M lock for some short
period of time. Because Sch-M is not compatible with Sch-S, reindexation was
locked by Sch-S. Next, all other queries to that table was also locked - by
Sch-M, because of algorithm which SQL Server use for processing queries when
encountering locks (described in Kalen Delaney's books).
Looking at Sch-S description, I can not understand why the lock, which
should be held for milliseconds, lasted for more than 7 hours.
"Uri Dimant" wrote:
> Hi
> Do not run reindex during the work hours. Moreover, examime only heavy
> fragmented tables/indexs abd defragment only them.
> Use alter index ... with option ONLINE (for more details see BOL)
>
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:EE853477-3366-4D83-B999-D86A7DB7C563@.microsoft.com...
> >I have SQL Server 2005 EE build 3186. And on the server several times
> > occurred errors caused by queries timeouts. When I look at locks, I see
> > what
> > the timeouts are caused by reindexation job, which rebuilding indices. The
> > job is blocked by other connection, which hold Sch-S lock on one of
> > tables.
> > The other connection belongs to one application which call one of stored
> > procedures. The stored procedure only do select from several tables, not
> > modify any data and not start any transactions.
> > sp_who2 shows what that application send last batch about 7 hours before
> > start of reindexation job. So the lock was bheld for 7 hours.
> > May someone tell, is it known problem? How it may be fixed or prevented?
>
>