Share
Report
10Hello,
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
All responses (
Answers (
