June 14, 2009 at 8:34 pm
Hi,
On SQL Server 2000 I used to have an every minute job which would log any blocking on the server into a table using a query below:
insert into blocks_history select
p.sql_handle as 'who_sql_handle', p.spid as 'who_spid', p.cmd as who_cmd, p.open_tran as 'who_open_tran',
p.loginame as 'who_loginame', p.program_name as 'who_program', p.hostname as 'who_host',
p.waittype as 'who_waittype', p.waittime as 'who_waittime', p.lastwaittype as 'who_lastwaittype',
p.waitresource as 'who_waitresource', p.dbid as 'who_dbid', p.status as 'who_status',
p.stmt_start as 'who_stmt_start',p.stmt_end as 'who_stmt_end',
pb.sql_handle as 'block_sql_handle', pb.spid as 'block_spid', pb.cmd as 'block_ cmd', pb.open_tran as 'block_open_tran',
pb.loginame as 'block_login', pb.program_name as 'block_program', pb.hostname as 'block_host',
pb.waittype as 'block_waittype', pb.waittime as 'block_waittime', pb.lastwaittype as 'block_lastwaittype',
pb.waitresource as 'block_waitresource', pb.dbid as 'block_dbid', pb.status as 'block_status',
pb.stmt_start as 'block_stmt_start',pb.stmt_end as 'block_stmt_end'
from master.dbo.sysprocesses p (nolock)
join master.dbo.sysprocesses pb (nolock) on pb.spid = p.blocked
where p.blocked <> 0
Just wondering if in SQL 2005 there is any system view which would give me similar results? Or may be there is another method to achieve the same?
Thanks.
June 14, 2009 at 8:50 pm
http://support.microsoft.com/kb/271509 has detailed query to find blockings.
Also master.sys.dm_tran_locks is a dymamic view that provides information about locks and blockings.
http://www.mssqltips.com/tip.asp?tip=1359
June 14, 2009 at 10:01 pm
ps (6/14/2009)
http://support.microsoft.com/kb/271509 has detailed query to find blockings.Also master.sys.dm_tran_locks is a dymamic view that provides information about locks and blockings.
That detailed query to find blocking is just another version of quering "master..sysprocesses".
As for master.sys.dm_tran_locks it gives information about locking, not blocking. It is also massive in terms of number of records returned. I only need a record per every process being blocked.
June 14, 2009 at 10:23 pm
sys.dm_os_waiting_task gives details of waiting task and sys.dm_tran_locks DMV gives detailed on locking tasks.
am trying to join these two views to get processes waiting due to a lock placed on the table.....
let me get back to you with the correct query.
June 15, 2009 at 2:22 am
What I am actually after is the most effective (performance wise) way to log any blocks on a server. Something I could run every minutes without killing a production system. On SQL Server 2000 using just master..sysprocesses was not a problem. I am not sure if this will be the same on SQL Server 2005/08. If those views are better or as efficient as master..sysprocesses but also provide more information (on SQL Server 2000 I used to run DBCC INPUTBUFFER for any blocked and blocking process discovered) it would be great.
June 15, 2009 at 3:45 am
SELECT DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id ,
tl.request_session_id 'who_session',who_es.program_name 'who_program', who_es.nt_domain 'who_nt_domain', who_es.nt_user_name 'who_user_name', who_es.login_name 'who_login_name',
wt.blocking_session_id 'blk_session',bl_es.program_name 'blk_program', bl_es.nt_domain 'blk_nt_domain', bl_es.nt_user_name 'blk_user_name', bl_es.login_name 'blk_login_name',
request_owner_id 'transaction_id', wt.resource_description
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_sessions bl_es on bl_es.session_id=tl.request_session_id
INNER JOIN sys.dm_exec_sessions who_es on who_es.session_id=wt.blocking_session_id
Do let us know if it comes close to your requirement:w00t:
June 15, 2009 at 7:33 pm
I've been trying to simulate some blocking, the query above does not return any results, while if I run this:
select spid, blocked from master..sysprocesses (nolock)
where blocked = 0 and
spid in (select blocked from master..sysprocesses (nolock) where blocked 0)
union
select spid, blocked from master..sysprocesses (nolock)
where blocked 0
I get something like this:
spid,blocked
51,0
73,51
So the blocking is obviously present, but the DMVs are not showing it...
June 15, 2009 at 10:12 pm
I did simulate the problem.
In window1 i wrote a query like this.
begin tran
insert into test1 values(1)
-- do not commit it
in window2, i login from a different userid and tried to select from that table
select * from test1
Obviously this process gets blocked as it waits for process from window1 to be completed and this query pinpoints the exact details.
sys.db_os_waiting_tasks dmv returns all processes which are waiting, some of which might be waiting for IO to be completed. The query i wrote wont show such processes, only blockings made by user queries.
You can query this dmv to see all waiting processes.
June 16, 2009 at 2:54 am
On the same example i posted in previus post i get similar results(same blocking transactions) from all three queries.
1. your initial query.
2. my query
3. your new query.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply