/*
================================================================================
Regression test: collect.process_blocked_process_xml  (blocked-process XML processor)
================================================================================
Guards the fixes in install/23_process_blocked_process_xml.sql:

  1. Mark-on-clean-run: events sp_HumanEventsBlockViewer legitimately yields 0
     rows for (self-blocks; non-lock GENERIC/NL waits such as a memory-grant
     RESOURCE_SEMAPHORE wait that tripped blocked_process_threshold) are marked
     is_processed = 1 instead of looping forever. The old @rows_parsed > 0 gate
     left them unprocessed and re-logged NO_RESULTS every cycle.

  2. Inclusive +1s window: a batch of events sharing one timestamp (MIN = MAX,
     the common case - a monitor loop emits every report at one instant) is
     parsed instead of dropped by the viewer's half-open event_time < @end_date.

Both sample events are stamped at the SAME sentinel timestamp (2099) so the run
exercises the single-timestamp window path directly.

USAGE: run against an IDLE PerformanceMonitor instance (no pending blocked-process
backlog) - e.g. a quiet test box. Aborts if a real backlog is present; cleans up
its own sentinel rows on exit. Requires sp_HumanEventsBlockViewer installed.

Samples are REAL captures: the two-session block came from a live server (spid
96 blocked by spid 100 on a KEY lock); the self-block is
a real non-lock self-referential report (spid 61). inputbuf text was
replaced with a placeholder to keep the script compact.
================================================================================
*/
USE PerformanceMonitor;
GO
SET NOCOUNT ON;

DECLARE
    @sentinel datetime2(7) = '2099-12-31 23:59:59.000',
    @fail integer = 0,
    @msg nvarchar(400),
    @raw_unprocessed integer,
    @chains integer,
    @self_chains integer,
    @status nvarchar(50);

/* Guard: an idle instance ensures the processor's auto-derive sees ONLY our rows. */
IF EXISTS (SELECT 1 FROM collect.blocked_process_xml WHERE is_processed = 0)
BEGIN
    RAISERROR('ABORT: this instance has pending unprocessed blocked-process rows. Run the regression on an idle test instance.', 16, 1);
    RETURN;
END;

/* Remove any sentinel residue from a previous aborted run. */
DELETE FROM collect.blocking_BlockedProcessReport WHERE event_time >= '2099-01-01';
DELETE FROM collect.blocked_process_xml WHERE event_time >= '2099-01-01';

/* Inject the two sample events at the same sentinel timestamp. */
INSERT INTO collect.blocked_process_xml (collection_time, event_time, blocked_process_xml, is_processed)
VALUES
    (@sentinel, @sentinel, CONVERT(xml, N'<event name="blocked_process_report" package="sqlserver" timestamp="2099-12-31T23:59:59.000Z"><data name="duration"><type name="uint64" package="package0"/><value>5058000</value></data><data name="database_id"><type name="int16" package="package0"/><value>7</value></data><data name="object_id"><type name="int32" package="package0"/><value>143</value></data><data name="index_id"><type name="uint32" package="package0"/><value>256</value></data><data name="lock_mode"><type name="lock_mode" package="sqlserver"/><value>5</value><text>X</text></data><data name="transaction_id"><type name="int64" package="package0"/><value>6403886</value></data><data name="resource_owner_type"><type name="resource_owner_type" package="sqlserver"/><value>0x00000001</value><text>LOCK</text></data><data name="blocked_process"><type name="xml" package="package0"/><value><blocked-process-report monitorLoop="7828"><blocked-process><process id="process2d1eaf25828" taskpriority="0" logused="404" waitresource="KEY: 7:72057594047299584 (3b1cab4d5f5d)" waittime="5058" ownerId="6403886" transactionname="user_transaction" lasttranstarted="2026-06-15T08:24:43.330" XDES="0x2d2228c0480" lockMode="X" schedulerid="12" kpid="4428" status="suspended" spid="96" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2026-06-15T08:24:43.330" lastbatchcompleted="2026-06-15T08:24:43.330" lastattention="1900-01-01T00:00:00.330" clientapp="Tcl 9.0 for Windows" hostname="NADAFACCIA" hostpid="26472" loginname="sa" isolationlevel="read committed (2)" xactid="6403886" currentdb="7" currentdbname="hammerdb_tpcc" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128048"><executionStack><frame line="102" stmtstart="7208" stmtend="7928" sqlhandle="0x03000700473a06598ba9a30051b4000001000000000000000000000000000000000000000000000000000000"/><frame line="1" stmtstart="186" stmtend="500" sqlhandle="0x0100070098f1db35c0de1e8bd202000000000000000000000000000000000000000000000000000000000000"/></executionStack><stackFrames><frame id="00" address="0x7FFAEEFC3BB4" pdb="ntdll.pdb" age="1" guid="A50EBE0F-5C64-17EE-BD90-F3106110D4F9" module="ntdll" rva="0xA3BB4"/><frame id="01" address="0x7FFAEC908269" pdb="kernelbase.pdb" age="1" guid="8DBB9127-6270-162F-46C8-F9428C49C22D" module="kernelbase" rva="0xC8269"/><frame id="02" address="0x7FFACFA770AD" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x70AD"/><frame id="03" address="0x7FFACFA76FCF" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x6FCF"/><frame id="04" address="0x7FFACFA737B2" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x37B2"/><frame id="05" address="0x7FFACFA731E3" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x31E3"/><frame id="06" address="0x7FFACFA73C00" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x3C00"/><frame id="07" address="0x7FFAD2F36E48" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x96E48"/><frame id="08" address="0x7FFAD2F36C01" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x96C01"/><frame id="09" address="0x7FFAD2EB917E" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x1917E"/><frame id="10" address="0x7FFAD2EBF55B" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x1F55B"/><frame id="11" address="0x7FFAD2EB9F0B" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x19F0B"/><frame id="12" address="0x7FFAD2ED70C9" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x370C9"/><frame id="13" address="0x7FFAD6511995" pdb="SqlTsEs.pdb" age="2" guid="E80FBDB1-EF5A-45FC-AE66-E8A17FC0C5CF" module="SqlTsEs" rva="0x1995"/><frame id="14" address="0x7FFAD2F213BB" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x813BB"/><frame id="15" address="0x7FFAD2ED289E" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x3289E"/><frame id="16" address="0x7FFAD2EE339E" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x4339E"/><frame id="17" address="0x7FFAD01D3C59" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x13C59"/><frame id="18" address="0x7FFAD01F6C20" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x36C20"/><frame id="19" address="0x7FFAD01F68D9" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x368D9"/><frame id="20" address="0x7FFAD058C123" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3CC123"/><frame id="21" address="0x7FFAD10C43B5" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xF043B5"/><frame id="22" address="0x7FFAD0561D0E" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3A1D0E"/><frame id="23" address="0x7FFAD01CC829" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xC829"/><frame id="24" address="0x7FFAD0210B0C" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x50B0C"/><frame id="25" address="0x7FFAD0210968" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x50968"/><frame id="26" address="0x7FFAD0210838" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x50838"/><frame id="27" address="0x7FFAD058C123" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3CC123"/><frame id="28" address="0x7FFAD10C43B5" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xF043B5"/><frame id="29" address="0x7FFAD0561D0E" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3A1D0E"/><frame id="30" address="0x7FFAD01CC829" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xC829"/><frame id="31" address="0x7FFAD01D7B29" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x17B29"/><frame id="32" address="0x7FFAD01C9D36" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x9D36"/><frame id="33" address="0x7FFAD01C9F02" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x9F02"/><frame id="34" address="0x7FFACFA79CF1" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x9CF1"/><frame id="35" address="0x7FFACFA79A1E" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x9A1E"/><frame id="36" address="0x7FFACFA79480" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x9480"/><frame id="37" address="0x7FFACFAA97D0" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x397D0"/><frame id="38" address="0x7FFACFAA985C" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x3985C"/><frame id="39" address="0x7FFACFAA9AE9" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x39AE9"/></stackFrames><inputbuf>SELECT 1; /*regression-sample*/</inputbuf></process></blocked-process><blocking-process><process status="suspended" waitresource="KEY: 7:72057594047299584 (3b1cab4d5f5d)" waittime="5068" spid="100" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2026-06-15T08:24:43.320" lastbatchcompleted="2026-06-15T08:24:43.320" lastattention="1900-01-01T00:00:00.320" clientapp="Tcl 9.0 for Windows" hostname="NADAFACCIA" hostpid="26472" loginname="sa" isolationlevel="read committed (2)" xactid="6403705" currentdb="7" currentdbname="hammerdb_tpcc" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128048"><executionStack><frame line="102" stmtstart="7208" stmtend="7928" sqlhandle="0x03000700473a06598ba9a30051b4000001000000000000000000000000000000000000000000000000000000"/><frame line="1" stmtstart="186" stmtend="500" sqlhandle="0x0100070098f1db35c0de1e8bd202000000000000000000000000000000000000000000000000000000000000"/></executionStack><stackFrames><frame id="00" address="0x7FFAEEFC3BB4" pdb="ntdll.pdb" age="1" guid="A50EBE0F-5C64-17EE-BD90-F3106110D4F9" module="ntdll" rva="0xA3BB4"/><frame id="01" address="0x7FFAEC908269" pdb="kernelbase.pdb" age="1" guid="8DBB9127-6270-162F-46C8-F9428C49C22D" module="kernelbase" rva="0xC8269"/><frame id="02" address="0x7FFACFA770AD" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x70AD"/><frame id="03" address="0x7FFACFA76FCF" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x6FCF"/><frame id="04" address="0x7FFACFA737B2" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x37B2"/><frame id="05" address="0x7FFACFA731E3" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x31E3"/><frame id="06" address="0x7FFACFA73C00" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x3C00"/><frame id="07" address="0x7FFAD2F36E48" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x96E48"/><frame id="08" address="0x7FFAD2F36C01" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x96C01"/><frame id="09" address="0x7FFAD2EB917E" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x1917E"/><frame id="10" address="0x7FFAD2EBF55B" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x1F55B"/><frame id="11" address="0x7FFAD2EB9F0B" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x19F0B"/><frame id="12" address="0x7FFAD2ED70C9" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x370C9"/><frame id="13" address="0x7FFAD6511995" pdb="SqlTsEs.pdb" age="2" guid="E80FBDB1-EF5A-45FC-AE66-E8A17FC0C5CF" module="SqlTsEs" rva="0x1995"/><frame id="14" address="0x7FFAD2F213BB" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x813BB"/><frame id="15" address="0x7FFAD2ED289E" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x3289E"/><frame id="16" address="0x7FFAD2EE339E" pdb="sqlmin.pdb" age="2" guid="803850A3-EE50-4621-86C8-7B3CD8BE2FE9" module="sqlmin" rva="0x4339E"/><frame id="17" address="0x7FFAD01D3C59" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x13C59"/><frame id="18" address="0x7FFAD01F6C20" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x36C20"/><frame id="19" address="0x7FFAD01F68D9" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x368D9"/><frame id="20" address="0x7FFAD058C123" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3CC123"/><frame id="21" address="0x7FFAD10C43B5" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xF043B5"/><frame id="22" address="0x7FFAD0561D0E" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3A1D0E"/><frame id="23" address="0x7FFAD01CC829" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xC829"/><frame id="24" address="0x7FFAD0210B0C" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x50B0C"/><frame id="25" address="0x7FFAD0210968" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x50968"/><frame id="26" address="0x7FFAD0210838" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x50838"/><frame id="27" address="0x7FFAD058C123" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3CC123"/><frame id="28" address="0x7FFAD10C43B5" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xF043B5"/><frame id="29" address="0x7FFAD0561D0E" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x3A1D0E"/><frame id="30" address="0x7FFAD01CC829" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0xC829"/><frame id="31" address="0x7FFAD01D7B29" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x17B29"/><frame id="32" address="0x7FFAD01C9D36" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x9D36"/><frame id="33" address="0x7FFAD01C9F02" pdb="sqllang.pdb" age="2" guid="A09A4BD6-488C-4F7F-B439-B3F88AF4CDCB" module="sqllang" rva="0x9F02"/><frame id="34" address="0x7FFACFA79CF1" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x9CF1"/><frame id="35" address="0x7FFACFA79A1E" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x9A1E"/><frame id="36" address="0x7FFACFA79480" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x9480"/><frame id="37" address="0x7FFACFAA97D0" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x397D0"/><frame id="38" address="0x7FFACFAA985C" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x3985C"/><frame id="39" address="0x7FFACFAA9AE9" pdb="SqlDK.pdb" age="2" guid="CE4A5810-8A51-44A0-AB86-209E679ADA43" module="SqlDK" rva="0x39AE9"/></stackFrames><inputbuf>SELECT 1; /*regression-sample*/</inputbuf></process></blocking-process></blocked-process-report></value></data><data name="database_name"><type name="unicode_string" package="package0"/><value>hammerdb_tpcc</value></data></event>'), 0),
    (@sentinel, @sentinel, CONVERT(xml, N'<event name="blocked_process_report" package="sqlserver" timestamp="2099-12-31T23:59:59.000Z"><data name="duration"><type name="uint64" package="package0"/><value>9046000</value></data><data name="database_id"><type name="int16" package="package0"/><value>0</value></data><data name="object_id"><type name="int32" package="package0"/><value>0</value></data><data name="index_id"><type name="uint32" package="package0"/><value>0</value></data><data name="lock_mode"><type name="lock_mode" package="sqlserver"/><value>0</value><text>NL</text></data><data name="transaction_id"><type name="int64" package="package0"/><value>0</value></data><data name="resource_owner_type"><type name="resource_owner_type" package="sqlserver"/><value>0x00000008</value><text>GENERIC</text></data><data name="blocked_process"><type name="xml" package="package0"/><value><blocked-process-report monitorLoop="184"><blocked-process><process id="process24723709848" taskpriority="0" logused="20000" waittime="9046" schedulerid="1" kpid="5000" status="suspended" spid="61" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2026-06-14T21:45:12.570" lastbatchcompleted="1900-01-01T00:00:00.570" lastattention="1900-01-01T00:00:00.570" clientapp="Core Microsoft SqlClient Data Provider" hostname="NADAFACCIA" hostpid="22720" loginname="sa" isolationlevel="read uncommitted (1)" xactid="196568" currentdb="6" currentdbname="PerformanceMonitor" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><executionStack><frame line="19" stmtstart="2194" stmtend="5166" sqlhandle="0x020000005aaca308ff748e3ce341135035aa926c858bf2210000000000000000000000000000000000000000"/></executionStack><inputbuf>SELECT 1; /*regression-sample*/</inputbuf></process></blocked-process><blocking-process><process status="suspended" waittime="9034" spid="61" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2026-06-14T21:45:12.570" lastbatchcompleted="1900-01-01T00:00:00.570" lastattention="1900-01-01T00:00:00.570" clientapp="Core Microsoft SqlClient Data Provider" hostname="NADAFACCIA" hostpid="22720" loginname="sa" isolationlevel="read uncommitted (1)" xactid="196568" currentdb="6" currentdbname="PerformanceMonitor" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><executionStack><frame line="19" stmtstart="2194" stmtend="5166" sqlhandle="0x020000005aaca308ff748e3ce341135035aa926c858bf2210000000000000000000000000000000000000000"/></executionStack><inputbuf>SELECT 1; /*regression-sample*/</inputbuf></process></blocking-process></blocked-process-report></value></data><data name="database_name"><type name="unicode_string" package="package0"/><value/></data></event>'), 0);

/* Run the processor (NULL dates -> auto-derive bounds from the unprocessed rows). */
EXEC collect.process_blocked_process_xml @debug = 1;

/* -------------------------------- assertions -------------------------------- */
SELECT @raw_unprocessed = COUNT(*) FROM collect.blocked_process_xml WHERE event_time = @sentinel AND is_processed = 0;
SELECT @chains = COUNT(*) FROM collect.blocking_BlockedProcessReport WHERE event_time >= '2099-01-01' AND blocking_spid IS NOT NULL;
SELECT @self_chains = COUNT(*) FROM collect.blocking_BlockedProcessReport WHERE event_time >= '2099-01-01' AND blocking_spid = 61;
SELECT TOP 1 @status = collection_status FROM config.collection_log WHERE collector_name = 'process_blocked_process_xml' ORDER BY collection_time DESC;

IF @raw_unprocessed = 0
    PRINT 'PASS 1: both sample events marked processed (self-block drains; no perpetual retry)';
ELSE
BEGIN SET @fail += 1; PRINT 'FAIL 1: a sentinel raw row was left unprocessed (stuck-retry not fixed)'; END;

IF @chains >= 1
    PRINT 'PASS 2: two-session block parsed to a chain (happy path + single-timestamp +1s window)';
ELSE
BEGIN SET @fail += 1; PRINT 'FAIL 2: two-session block did NOT parse - inclusive-window or happy-path regression'; END;

IF @self_chains = 0
    PRINT 'PASS 3: self-block produced no chain (correctly filtered, yet still marked processed)';
ELSE
BEGIN SET @fail += 1; PRINT 'FAIL 3: self-block leaked a chain row (spid 61)'; END;

IF @status = 'SUCCESS'
    PRINT 'PASS 4: collection_log status = SUCCESS (not the old perpetual NO_RESULTS)';
ELSE
BEGIN SET @fail += 1; SET @msg = 'FAIL 4: collection_log status = ' + ISNULL(@status, '(null)') + ' (expected SUCCESS)'; PRINT @msg; END;

/* --------------------------------- cleanup ---------------------------------- */
DELETE FROM collect.blocking_BlockedProcessReport WHERE event_time >= '2099-01-01';
DELETE FROM collect.blocked_process_xml WHERE event_time >= '2099-01-01';

IF @fail = 0
    PRINT '=== RESULT: ALL PASS ===';
ELSE
BEGIN
    SET @msg = '=== RESULT: ' + CONVERT(nvarchar(10), @fail) + ' assertion(s) FAILED ===';
    RAISERROR(@msg, 16, 1);
END;
GO
