I need to create a report (historic) on blocking query details through SAM. With below query, I got details of current blockings. But I need historic details of blockings happened. Can anyone help me on this ?
select a.session_id ,
b.session_id ,
sql_text.text ,sql_btext.text ,c.wait_type e from sys.dm_exec_requests a
inner join sys.dm_exec_requests b
on a.blocking_session_id = b.session_id
cross apply (select * from sys.dm_exec_sql_text(b.sql_handle)
) sql_btext
cross apply
(select * from sys.dm_exec_sql_text(a.sql_handle)
) sql_text
inner join sys.dm_os_waiting_tasks c
on c.session_id=b.session_id