Maybe also ping the dbatools team with your findings !

That will only get you the currently cached plans.

You may want to activate QueryStore to have a better view over a given time frame.

Don't convert a columns data type if it is not needed

If conversion is needed, convert to the correct data type.

Declare @tb table ( FADate date not null,...

Double check the SSIS package, and see if it starts by clearing the table and/or does not overwrite existing data! #MyGuess

Maybe have a look at Gianluca's WorkloadTools to process the xe event files in bulk

"WorkloadTools : A collection of tools to collect, analyze and replay SQL Server workloads, on...

Keep in mind: Not just any kind of Azure BLOB storage! It needs to be V2 ! ( or newer )

And you have to remove these files yourself ! (...

Docs state this: "... ALTER ANY DATABASE EVENT SESSION permission in the database."

Did you test that ?

What you're trying to do doesn't make much sense to me, but you need to build "dynamic sql" for the "create view" statement you are trying to generate and implement.

indeed, nowadays DBATOOLS is the way to go!

https://docs.dbatools.io/Restore-DbaDatabase

DTC just coördinaties the transactions!

Stay away of linked servers as much as you can!

Chances are the sql instance that runs your sql statement ( join with linked server involved )...

If you need it quick and want to avoid buying the wrong hardware ....

Go for an Azure SQLDatabase ! ( subscription based, flexible, shrink/enlarge capacity as you need)

Add and AzureVM...

What are the criteria with which you determine a row to be red or green ?

did you try a solution using a windowing function ?

WITH cteRwNos
     AS (SELECT a1.[TxID]
         ...

Posting your solution would be very much appreciated.

did you check with Common Causes and Troubleshooting Solutions for SQL AG Data Synchronization Latency