SQLSERVER 查询阻塞SQL以及锁_MsSqlserver

查询阻塞sql以及锁

查阻塞语句

--查阻塞语句
select text, * from sys.dm_exec_requests   er cross apply  sys.dm_exec_sql_text(er.sql_handle)

查阻塞与锁

--查阻塞与锁
select t1.resource_type as [锁类型], db_name(resource_database_id) as [数据库名],
t1.resource_associated_entity_id as [阻塞资源对象],t1.resource_description as [资源描述信息], t1.request_mode as [请求的锁], 
t1.request_session_id as [等待会话], t2.wait_duration_ms as [等待时间],       
(select [text] from sys.dm_exec_requests as r with (nolock)                      
	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, 
		(case when r.statement_end_offset = -1 
		then len(convert(nvarchar(max), qt.[text])) * 2 
		else r.statement_end_offset end )/2) 
	from sys.dm_exec_requests as r with (nolock)
	cross apply sys.dm_exec_sql_text(r.[sql_handle]) as qt
	where r.session_id = t1.request_session_id
) as [等待会话执行的sql],                    
t2.blocking_session_id as [阻塞会话],                                        
(select [text] from sys.sysprocesses as p                                       
	cross apply sys.dm_exec_sql_text(p.[sql_handle]) 
	where p.spid = t2.blocking_session_id
) as [阻塞会话执行的批sql]
from sys.dm_tran_locks as t1 with (nolock)
inner join sys.dm_os_waiting_tasks as t2 with (nolock)
on t1.lock_owner_address = t2.resource_address option (recompile);

查询语句执行情况

select top 1000 t.hostname,
                t.loginame,
                percent_complete,
        [session_id] ,
        der.[request_id] ,
        [start_time] as '开始时间' ,
        der.[status] as '状态' ,
        [command] as '命令' ,
        dest.[text] as 'sql语句' ,
        db_name([database_id]) as '数据库名' ,
        [blocking_session_id] as '正在阻塞其他会话的会话id' ,
        [wait_type] as '等待资源类型' ,
        [wait_time] as '等待时间' ,
        [wait_resource] as '等待的资源' ,
        [reads] as '物理读次数' ,
        [writes] as '写次数' ,
        [logical_reads] as '逻辑读次数' ,
        [row_count] as '返回结果行数',
                t.hostname,
                t.loginame
from    sys.[dm_exec_requests] as der
        cross apply sys.[dm_exec_sql_text](der.[sql_handle]) as dest
                left join sys.sysprocesses t on t.spid=der.session_id
where   [session_id] > 50
       -- and db_name(der.[database_id]) = 'gposdb'
order by [cpu_time] desc