December 22, 2009 at 3:58 am
how to identify blocking problems in sqlserver 2005
December 22, 2009 at 5:08 am
'BlKBy' column of sp_who2 (stored procedure) should tell you that!
---------------------------------------------------------------------------------
December 22, 2009 at 9:45 am
You can also use the Activity Monitor GUI, then look at details to see what's running.
December 22, 2009 at 12:58 pm
there should be a number of scripts on this site designed to monitor for blocking, many actively monitor for blocks of a certain length and fire off code to store details of what was happening.
they are usually (always?) based on the blocked column in sys.sysprocesses having a non-null value, indicating that connection is blocked by the spid recorded in the blocked column.
you want 3 main bits of info, the blocking chain from sys.sysprocesses, locks held from sp_lock and the actual SQL being run. That can be obtained by querying sys.dm_exec_sessions and sys.dm_exec_requests and tying into sys.dm_exec_sql_text
Sample bit of code I stole from somewhere which is like sp_who +. (example of whats out there if you look)
SELECT D.text SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) Status, A.login_name Login, A.host_name HostName, C.BlkBy, DB_NAME(B.Database_ID) DBName, B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime, ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO, A.last_request_start_time LastBatch, A.program_name
FROM sys.dm_exec_sessions A LEFT JOIN sys.dm_exec_requests B ON A.session_id = B.session_id
LEFT JOIN ( SELECT A.request_session_id SPID, B.blocking_session_id BlkBy FROM sys.dm_tran_locks as A INNER JOIN sys.dm_os_waiting_tasks as B ON A.lock_owner_address = B.resource_address ) C ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply