Dynamics Community Forum Thread Details

Hello,

We are facing blocking latencies in the production environment, it happens periodically every 15minutes or so. when I run the live query in the health metrics on LCS I have the following message :

as
(
select spid, '' as parentspid, program_name as spidprogramname,hostname from sys.sysprocesses (NOLOCK) a where blocked = 0 and dbid = DB_ID()
and spid in ( select blocked from sys.sysprocesses a where blocked 0) and dbid = DB_ID()
and program_name not like '%DAMS%'
union
select spid, blocked as parentspid, program_name as spidprogramname, hostname from sys.sysprocesses (NOLOCK) where blocked 0
and dbid = DB_ID()
and program_name not like '%DAMS%'
),
blocksinfo2
as
(select *, cast(spid as nvarchar(30)) AS hierarchy from blocksinfo1 where parentspid = 0
union all
select m.*, cast((hierarchy+'.'+cast(m.spid as nvarchar(30))) as NVARCHAR(30)) AS hierarchy
from blocksinfo1 m join blocksinfo2 on m.parentspid = blocksinfo2.spid)
SELECT r.*,
ISNULL(BLOCKED.wait_time, 0) AS wait_time,
CASE
WHEN BLOCKED.wait_resource like 'objectk%' THEN 'Object'
WHEN BLOCKED.wait_resource like 'page%' THEN 'Page'
WHEN BLOCKED.wait_resource like 'key%' THEN 'Key'
WHEN BLOCKED.wait_resource like 'rid%' THEN 'Row'
ELSE 'N/A'
END AS wait_resource,
BLOCKEDSQL.text AS SQLText,
ISNULL((BLOCKED.total_elapsed_time), 0) AS DurationInMilliSeconds into #WhatsRunning
FROM blocksinfo2 r
left outer join sys.dm_exec_requests blocked on blocked.session_id = r.spid
OUTER APPLY sys.dm_exec_sql_text(BLOCKED.sql_handle) AS BLOCKEDSQL
WHERE blocked.database_id = db_id()
Order by hierarchy/>with blocksinfo1 as ( select spid, '' as parentspid, program_name as spidprogramname,hostname from sys.sysprocesses (NOLOCK) a where blocked = 0 and dbid = DB_ID() and spid in ( select blocked from sys.sysprocesses a where blocked <> 0) and dbid = DB_ID() and program_name not like '%DAMS%' union select spid, blocked as parentspid, program_name as spidprogramname, hostname from sys.sysprocesses (NOLOCK) where blocked <> 0 and dbid = DB_ID() and program_name not like '%DAMS%' ), blocksinfo2 as (select *, cast(spid as nvarchar(30)) AS hierarchy from blocksinfo1 where parentspid = 0 union all select m.*, cast((hierarchy+'.'+cast(m.spid as nvarchar(30))) as NVARCHAR(30)) AS hierarchy from blocksinfo1 m join blocksinfo2 on m.parentspid = blocksinfo2.spid) SELECT r.*, ISNULL(BLOCKED.wait_time, 0) AS wait_time, CASE WHEN BLOCKED.wait_resource like 'objectk%' THEN 'Object' WHEN BLOCKED.wait_resource like 'page%' THEN 'Page' WHEN BLOCKED.wait_resource like 'key%' THEN 'Key' WHEN BLOCKED.wait_resource like 'rid%' THEN 'Row' ELSE 'N/A' END AS wait_resource, BLOCKEDSQL.text AS SQLText, ISNULL((BLOCKED.total_elapsed_time), 0) AS DurationInMilliSeconds into #WhatsRunning FROM blocksinfo2 r left outer join sys.dm_exec_requests blocked on blocked.session_id = r.spid OUTER APPLY sys.dm_exec_sql_text(BLOCKED.sql_handle) AS BLOCKEDSQL WHERE blocked.database_id = db_id() Order by hierarchy

Did anyone face any similar issue and has any idea what causes this ? This is blocking and waiting for MS reply

Thank you