> Hi,
>
> we are seeing increasing apparently not logical locking chains on two
> of our SQL Server 2000 installations (SP4). sp_who2 will reveal results
> like:
>
> SPID      HostName   BlkBy
> -----     ---------- -----
> 930    WWW01       960
> 930    WWW01       960
> 960    WWW01       960
>
> ...sometimes circluar like:
>
> SPID      HostName   BlkBy
> -----     ---------- -----
> 107    WWW01       520
> 520    WWW02       921
> 921    WWW03       107
>
> We have tried to analyze what is initially causing the blocks but it
> always seems to be a different and usually innocent statement like a
> simple SELECT. The locks also seem to escalate strangely and I have
> even seen exlusive DB locks on tempdb held by a definitly select
> statement with nolock option. (We are using db recovery option "simple"
> and typically place a "set transaction isolation level read
> uncommitted" first in every stored proc.)
>
> Usually rebooting the server (not restarting SQL Server service) will
> help for a while, but the problem lately reappears sooner and sooner.
>
> There hasn't been any change in the way we update or select the data,
> so I guess the database (or the server???) are degrading somehow?
>
> I would be most thankful for any idea about this.
>
> Regards
> DC

> Can you post the query?
>
> I've found that a lot of times developers omit either the with or the
> parens (you can omit one or the other, but not both).  If you simply
> write:
>
> select * from Table nolock
>
>
> SQL treats the nolock as the table alias and not as a optimizer hint.
>
> It should be written as:
>
> select * from table (nolock)
> or
> select * from table with nolock
> or
> select * from table with (nolock)
>
> Joe
>
> DC wrote:
> > Hi,
> >
> > we are seeing increasing apparently not logical locking chains on two
> > of our SQL Server 2000 installations (SP4). sp_who2 will reveal results
> > like:
> >
> > SPID      HostName   BlkBy
> > -----     ---------- -----
> > 930    WWW01       960
> > 930    WWW01       960
> > 960    WWW01       960
> >
> > ...sometimes circluar like:
> >
> > SPID      HostName   BlkBy
> > -----     ---------- -----
> > 107    WWW01       520
> > 520    WWW02       921
> > 921    WWW03       107
> >
> > We have tried to analyze what is initially causing the blocks but it
> > always seems to be a different and usually innocent statement like a
> > simple SELECT. The locks also seem to escalate strangely and I have
> > even seen exlusive DB locks on tempdb held by a definitly select
> > statement with nolock option. (We are using db recovery option "simple"
> > and typically place a "set transaction isolation level read
> > uncommitted" first in every stored proc.)
> >
> > Usually rebooting the server (not restarting SQL Server service) will
> > help for a while, but the problem lately reappears sooner and sooner.
> >
> > There hasn't been any change in the way we update or select the data,
> > so I guess the database (or the server???) are degrading somehow?
> >
> > I would be most thankful for any idea about this.
> >
> > Regards
> > DC