what did you expect?
you have the function in the where clause which means it will be called for each row and then once more for each output row.
it...

Do you need to include the survey_id in the nonclustered index?

The nonclustered indexes contain a reference to the primary key if there's one and in your case it seems the...

in order to get some help you may need to post the table structures, the indexes and some info about the data distribution

and for the second question, peer to peer is async

how can an update happen on a row that's not there ?

well then, ignore the first line of my previous post 🙂

So your SSIS package is getting killed in the deadlock

This means the various tasks get in deadlocks with other processes on the server and because usually SSIS (ETL) transactions...

write the dimension maintenance in tSQL