Here's some rough accounting of where the time was spent in your execution plan:
- About 5 seconds to join CohortStagingTemp, #Cohort_5, and #Cohort_4 together with batch mode hash joins
- About 42 seconds to join #Cohort_1
- About 15 seconds to join in the remaining 3 tables
- About 16 seconds for the parallel insert
So there's a clear bottleneck in the query: the join to #Cohort_1. SQL Server is estimating a 12 KB row size from that table, so I'm guessing that you have some LOB columns in that table. There are some restrictions on LOB data for batch mode hash joins. That's probably why you see some batch mode hash joins and then the plan switches to row mode with the expensive merge join along with other row mode hash joins.
In general, I would stick with one approach: either get a query plan with row mode merge joins or get a plan with mostly batch mode hash joins. Your tables all have the same row count and join on the same column so merge joins might do just fine here, but the query may need to run at MAXDOP 1 for best performance. I recommend getting actual query plans for the following test cases:
- Try the query as is but comment out the join to #Cohort_1
- Try to get a better hash join plan by adding an
OPTION (HASH JOIN, LOOP JOIN) hint and rewriting the query to have the #Cohort_1 join at the end
- Add clustered indexes to all tables on the PersonID column and add an
OPTION (MERGE JOIN) hint to the query
You also expressed concern that the query can take up to 40 minutes during ETL which is much longer than what you observed with your testing. The most basic way to troubleshoot this is to enable Query Store and to look for differences between what query store logs and what you saw in your own testing. Are the query plans different? Are the wait stats different? Does one query use more CPU than the other? All of that information is available on SQL Server 2019. You don't have to guess as to whether or not there was blocking. Query Store will tell you this.
In a comment, you said the following:
our admins won't allow us to turn on query store d/t overhead which is disappointing
I find this to be an offensive statement. DBAs don't work to serve the database. They should work to serve the end users. The end users include you, the developer. If they want to take the position that they can't enable Query Store due to overhead (which is almost certainly wrong), then they need to provide you the information that you need in some other way. You may need to escalate this problem to management or try to find some kind of compromise. For example, if the ETL workload only runs at night maybe Query Store can be enabled just during the night. But I guarantee you that if the DBAs had a problem that they were responsible for, they would enable the necessary diagnostics instead of making excuses about overhead.