sql server2017 block分析

select
tl.resource_type as [Resource Type]
,db_name(tl.resource_database_id) as [DB Name]
,case tl.resource_type
when 'OBJECT' then
object_name
(
tl.resource_associated_entity_id
,tl.resource_database_id
)
when 'DATABASE' then 'DB'
else
case when tl.resource_database_id = db_id()
then
( select object_name(object_id, tl.resource_database_id)
from sys.partitions
where hobt_id = tl.resource_associated_entity_id )
else '(Run under DB context)'
end
end as [Object]
,tl.resource_description as [Resource]
,tl.request_session_id as [Session]
,tl.request_mode as [Mode]
,tl.request_status as [Status]
,wt.wait_duration_ms as [Wait (ms)]
,qi.sql
,qi.query_plan
from
sys.dm_tran_locks tl with (nolock) left outer join
sys.dm_os_waiting_tasks wt with (nolock) on
tl.lock_owner_address = wt.resource_address and
tl.request_status = 'WAIT'
outer apply
(
select
substring(s.text, (er.statement_start_offset / 2) + 1,
(( case er.statement_end_offset
when -1
then datalength(s.text)
else er.statement_end_offset
end - er.statement_start_offset) / 2) + 1) as sql
, qp.query_plan
from
sys.dm_exec_requests er with (nolock)
cross apply sys.dm_exec_sql_text(er.sql_handle) s
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where
tl.request_session_id = er.session_id
) qi
where
tl.request_session_id <> @@spid
order by
tl.request_session_id
option (recompile)

---------------------只显示有问题的

Filtering out blocked and blocking session information 

select
tl1.resource_type as [Resource Type]
,db_name(tl1.resource_database_id) as [DB Name]
,case tl1.resource_type
when 'OBJECT' then
object_name
(
tl1.resource_associated_entity_id
,tl1.resource_database_id
)
when 'DATABASE' then 'DB'
else
case when tl1.resource_database_id = db_id()
then
(
select
object_name(object_id, tl1.resource_database_id)
from sys.partitions
where hobt_id = tl1.resource_associated_entity_id
)
else '(Run under DB context)'
end
end as [Object]
,tl1.resource_description as [Resource]
,tl1.request_session_id as [Session]
,tl1.request_mode as [Mode]
,tl1.request_status as [Status]
,wt.wait_duration_ms as [Wait (ms)]
,qi.sql
,qi.query_plan
from
sys.dm_tran_locks tl1 with (nolock) join
sys.dm_tran_locks tl2 with (nolock) on
tl1.resource_associated_entity_id = tl2.resource_associated_entity_id
left outer join sys.dm_os_waiting_tasks wt with (nolock) on
tl1.lock_owner_address = wt.resource_address and
tl1.request_status = 'WAIT'
outer apply
(
select
substring(s.text, (er.statement_start_offset / 2) + 1,
(( case er.statement_end_offset
when -1
then datalength(s.text)
else er.statement_end_offset
end - er.statement_start_offset) / 2) + 1) as sql
, qp.query_plan
from
sys.dm_exec_requests er with (nolock)
cross apply sys.dm_exec_sql_text(er.sql_handle) s
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where
tl1.request_session_id = er.session_id
) qi
where
tl1.request_status <> tl2.request_status and
(
tl1.resource_description = tl2.resource_description or
(
tl1.resource_description is null and
tl2.resource_description is null
)
)
option (recompile)