select spid 进程,STATUS 状态, 登录帐号=SUBSTRING(SUSER_SNAME(sid),1,30),用户机器名称=SUBSTRING(hostname,1,12),是否被锁住=convert(char(3),blocked)
,数据库名称=SUBSTRING(db_name(dbid),1,20),cmd 命令,waittype as 等待类型,last_batch 最后批处理时间,open_tran 未提交事务的数量
from master.sys.sysprocesses
where open_tran>0 order by last_batch desc
select t1.resource_type [资源锁定类型],DB_NAME(resource_database_id) as 数据库名
,t1.resource_associated_entity_id 锁定对象,t1.request_mode as 等待者请求的锁定模式
,t1.request_session_id 等待者SID
,t2.wait_duration_ms 等待时间
,(select TEXT from sys.dm_exec_requests r cross apply
sys.dm_exec_sql_text(r.sql_handle) where r.session_id=t1.request_session_id) as 等待者要执行的SQL
,(select SUBSTRING(qt.text,r.statement_start_offset/2+1,
(case when r.statement_end_offset=-1 then DATALENGTH(qt.text) else r.statement_end_offset end -r.statement_start_offset)/2+1)
from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle)qt
where r.session_id=t1.request_session_id) 等待者正要执行的语句
,t2.blocking_session_id [锁定者SID]
,(select TEXT from sys.sysprocesses p cross apply
sys.dm_exec_sql_text(p.sql_handle)
where p.spid=t2.blocking_session_id
) 锁定者执行语句
from sys.dm_tran_locks t1,sys.dm_os_waiting_tasks t2
where t1.lock_owner_address=t2.resource_address
-- kill 75
SELECT
SPID = er.session_id
,Status = ses.status
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
ORDER BY
er.blocking_session_id DESC
,er.session_id
