Как посмотреть процессы и их блокировки в SQL 2008

select * from sys.dm_tran_locks
select * from sys.dm_exec_requests
select * from sys.dm_exec_sessions

declare
@DbId as int = db_id(N'Имя вашей базы')

use [master];

select
[now] = cast(sysdatetime() as datetime2(0))
/*, [db_name] = db_name(er.[database_id]) */
, es.session_id
, er.[status]
, er.command
/*, [sql_command] = case when er.sql_handle is null then null else (select [text] from sys.dm_exec_sql_text(er.sql_handle)) end*/
, er.percent_complete
, er.[wait_resource]
, er.blocking_session_id
, es.[program_name]
, es.[host_name]
, es.[login_name]
, es.[nt_user_name]
, er.start_time
, er.[user_id]
, er.connection_id
from
sys.dm_exec_requests er
inner join
sys.dm_exec_sessions es
on es.[session_id] = er.[session_id]
where
er.database_id = @DbId
;