blocking in SQL server

 we have a vendor's application, has blocking happens very often, at lease once a week. Without proper tool to trace the application's activity to prove what we believe or what we should suggest to change, cannot get the transaction start as we noticed the blocking ,the statement already passed, 

We noticed that the application kept 2 transactions open without commit or rollback, in the transactions, there are a few locks held on page, row levels ,and tens of other sessions were blocked.

 submitted the request to Microsoft, suggested using the RCSI level, with the expense of tempdb size and some IO overload.

to find the statements in extended event, I searched online https://www.sqlserver-dba.com/2019/09/how-to-read-and-parse-the-microsoft-sql-server-extended-event-log-file-xel-file.html 

and used something below:

--Read the Extended Event Log File output file and PARSE the returned xml format rows  

DECLARE @path NVARCHAR(260);

 

----retrieve the local path of system_health files

 

--SELECT @path = dosdlc.path

 

--FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;

 

SET @path = N'H:\pssd\output0\xxxx__pssdiag_xevent_0_132693597627810000.xel';

--SELECT CAST(fx.event_data AS XML) AS Event_Data,

-- fx.object_name

-- FROM sys.fn_xe_file_target_read_file(@path,

-- NULL,

-- NULL,

-- NULL) AS fx

-- where object_name='sql_batch_starting'

-- ;

SELECT

 

n.value('(@name)[1]', 'varchar(50)') as event_name,

 

n.value('(@package)[1]', 'varchar(50)') AS package_name,

 

n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],

 

n.value('(data[@name="duration"]/value)[1]', 'int') as duration,

 

n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,

 

n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,

 

n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,

 

n.value('(data[@name="writes"]/value)[1]', 'int') as writes,

 

n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,

 

n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,

 

n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,

 

n.value('(data[@name="offset"]/value)[1]', 'int') as offset,

 

n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,

 

n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,

 

n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,

 n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') as client_app_name,

 n.value('(action[@name="session_id"]/value)[1]', 'int') as session_id,

 n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(128)') as sql_text

FROM(select cast(event_data as XML) as event_data

 

FROM sys.fn_xe_file_target_read_file(@path, null, null, null)) fx

 

CROSS APPLY fx.event_data.nodes('event') as q(n)

 

WHERE n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') = 'xxxxxxx'

and  n.value('(action[@name="session_id"]/value)[1]', 'int') = '995';