February 14, 2010 at 9:30 pm
Hi,
In the production system if you find block or Lock what are the action an DBA should take ?
Can you explain these steps in detail ?
February 15, 2010 at 12:12 am
Generally nothing.
Locking is a normal part of DB activity and is essential for ensuring data consistency across the database. There's nothing wrong with locks.
Blocking's also normal. Means that one process wants to access some data that another process is busy with. This is again expected, without this there would be no consistency in the data.
Long duration blocking that's affecting the system's performance may be a problem. The short term (bad) solution is often to kill the blocking process. Only do that if you know what that process is and what the impact of rolling it back will be.
The better solution to long-term blocking is to identify the queries that often cause blocking and see if they can be optimised in any way. Tune the query or tune the index so that it performs better.
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
February 16, 2010 at 7:49 am
I just want to add a link to my normal reference for such questions:
Microsoft Waits & Queues Methodology
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply