May 14, 2008 at 1:59 am
Hi,
i have a user do a system posting but it is take a long time , i use sp_who and found out it is blocking by other user. so anyone got idea how to prevent this happen again???
Regards,
Patrick
May 14, 2008 at 2:14 am
Depends
What was the blocked process doing?
What was the blocking process doing?
Generally you fix blocking problems by optimising code and ensuring that indexes are adequate.
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
May 14, 2008 at 2:29 am
The blocked process is inserted data to table A.
The other blocking process is viewing the table A.
Data can be inserted once i inform the user who viewing the table logout the system.
Appreciate your reply.
May 14, 2008 at 2:33 am
Readers take shared locks. Changing the data requires an exclusive lock, which is incompatible with a shared lock. Hence the blocked process waits.
It shouldn't have to wait long though. As soon as the read of the table finishes, the lock should be released, regardless of whether the user is still conencted or not. If that's not happening, then something strange is going on.
How big's the table? Is the view of the table the entire table? A query on the table?
What application? Is a transaction been left open with locks held longer than necessary?
p.s. Please post SQL 2000 questions in the SQL 2000 forums. Posting in the 2005 forums may get you suggestions that only work on 2005
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
May 14, 2008 at 2:44 am
Table content is 439518 rows inside.
This issue was first time happen on my new server, and it was never happen before on my
other database server.
Is there any possible of the configuration in Enterprise manager or
have any script to housekeeping it, like DBCC.
Regards,
Patrick
May 14, 2008 at 2:54 am
Maybe.
Check that your indexes are not fragmented and that your statistics are up to date.
Check that implicit transactions is not a connection default.
You can check the execution plan of the read query on the new server and old, see if there's any difference.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply