Difference Between Blocking and Waiting
-
Can someone please explain the difference between blocking and waiting? I am running SQL Server 2005 svs pack 2.
thanks in advance
-
Blocking is a manifestation of the logical locks that are needed to maintain the transactional consistency.
When the wait for locks exceed a threshold, it may impact the response time. You can use BlockedProcessThreshold configuration parameter to establish a user configured sql instance block threshold. You may want to monitor your blockings with sql profiler - use the Blocked Process Report event class under errors and warnings object.
sys.dm_db_index_operational_stats is used in sql 2005 for getting a detailed accounting of lock statistics per table, index, eventualy partition.
In conclusion, blocking is primarily waits for logical locks, such as the wait to acquire an X lock on a resource OR the wiats that results from latches. Logical lock waits occur when a request to acquire a non-compatible lock on an already locked resorce is made. This mechanism is needed to provide the data consistency based on the transaction isolation level at which a query statement is running.
For a complete list of sql 2005 wait types see the topic sys.dm_os_wait_stats (view) in BOL.
Hope this helps.
-
If you are blocking, you are causing someone else to wait. The reasons for blocking and waiting were explained correctly by michaela.
-
Michael has explained well about blocking and I have found some good KB which will also help you to understand blocking and how to troubleshoot it.
http://support.microsoft.com/kb/224453/en-us
Minaz
"More Green More Oxygen !! Plant a tree today"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply