|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Strange blocking problemswe 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: Show quoteHide quote > 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 Hi Joe,
thanks for the answer, but I am quite sure that's not it. We usually just insert set nocount on set transaction isolation level read uncommitted at the start of every stored proc and each dynamic sql string. Regards DC Joe wrote: Show quoteHide quote > 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  
Other interesting topics
Primary Key
Query working in EM and QA but not in Store proc SQL Server Express w/ C# 2.0... Cannot find stored procedure... Triggers: using checksum() Please help me with a SELECT What is the search query for that? How do I place multiple records of a sub-query in one record? using dbcc on linked server xp_cmdshell doesn't work arranging month from january to december |
|||||||||||||||||||||||
