0

We know that there is a "blocked_process_report" event, which will be fired once the blocked session is blocked longer than a threshold defined by

sp_configure 'blocked process threshold'

However, it seems there is no corresponding "unblocking" event, so if want to find the blocking duration for a specific blocking event, what should I do?

I know we can use sys.dm_index_operational_stats to get some aggregated blocking duration data, but it is not accurate/detailed enough for me.

Thanks for your help.

Kind regards,

jeff_yao

  • 1
    This trace event doesn't fire, so to speak. It is a polling process. So, using the event would require you to capture the first instance of a blocking situation and also the last one and calc time diff between the two. And, of course, it would only be as accurate as the frequency for the polling interval allows. Did you dig inte Extended Even and see if there is some other event that can be helpful? – Tibor Karaszi Jul 11 '19 at 18:56
  • Thanks @TiborKaraszi, "blocked_process_event" is indeed fired and I even captured it via event notification. I also researched XE select * from sys.dm_xe_objects where object_type = 'event' and name like '%block%' in sql server 2017 and do not see anything helpful. I really think MS should add a corresponding event. – jyao Jul 11 '19 at 19:26
  • 1
    My point is that even when you are using an eventing engine, it is in the end a polling process. It is the deadlock monitor that polls by default every 5 seconds that also does this work. So, at the engine level this isn't event driven, it is a polling process. – Tibor Karaszi Jul 11 '19 at 20:21
  • Agree, @TiborKaraszi. Yes, in that sense, it is polling process inside sql server engine. I am contemplating a solution that may not be accurate to millisecond level but should be good enough for half second level. – jyao Jul 11 '19 at 21:06
0

Can you please try the below query. Hope this helps. Capture the result in a table for more visibility.

SELECT TOP 20
                CAST (LEFT( sp.spid + SPACE(60), 20) AS CHAR(20))  AS SPID_VAL,
       CAST(LEFT( sp.blocked + SPACE(60), 30) AS CHAR(30))  AS BLOCKING_SPID,
       CAST(LEFT ( LTRIM(RTRIM(loginame)) + SPACE(60), 60) AS CHAR(60)) AS LOGIN_NM,
                CAST(LEFT( RTRIM(LTRIM(DB_NAME(dbid))) + SPACE(60), 30) AS CHAR(30)) AS DATABASE_NM, 
                LEFT(hostname + SPACE(60), 30) AS HOSTNAME_NM,
                 (SELECT  LEFT(hostname + SPACE(60), 30) FROM [master].dbo.sysprocesses sp1  WHERE sp1.spid = sp.blocked) Blocking_Hostname,
                  (SELECT   CAST(LEFT ( LTRIM(RTRIM(loginame)) + SPACE(60), 60) AS CHAR(60)) FROM [master].dbo.sysprocesses sp1  WHERE sp1.spid = sp.blocked) Blocking_Login,
                  GETDATE() AS [Date]
                FROM sys.sysprocesses sp
WHERE dbid > 0 
AND status NOT IN ( 'background')  
AND blocked <> 0 
  • No, this does not help. This query find you only the blocking sessions. BTW, your query is too messy (sorry to say so), you can use self joins to write a cleaner and easy-to-understand query. – jyao Jul 11 '19 at 19:30
  • @jyao, thanks for your suggestion. – l.lijith Jul 11 '19 at 19:33
0

There is no way to know the "total blocking duration". What you can do though, is run a job task each 10 seconds to keep checking if there are processes generating locks over X seconds, and send you by email the details of the query. That will give you an idea of the blocking duration time.

  • This is a commonly conceived solution, but really not what I am looking for. – jyao Jul 11 '19 at 21:13
  • Yeah. I know! But, that's why I said there is now way to get this info. – Danilo Braga Jul 11 '19 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.