--Extend this index to make it coverable for the query:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_did_uid_sid] ON [Dim].[UserDevice]

(

[DeviceID] ASC,

[UserID] ASC,

[SubscriberID] ASC

)

INCLUDE(

Locale,[LastUsedDate]

)

--Create this index

CREATE UNIQUE NONCLUSTERED INDEX [UIX_NCSTransaction_IDs] ON [Ext].[NCSTransaction]

(

[SubscriberID],[UserID],[DeviceID]

)

INCLUDE ([Locale],location,[TransactionStartDateTime])

If the "select" part of the merge statement takes 4 minutes, then you'd better replace that code with a temp table and put the second index on the temp table.

I think there is a chance to improve it a lot. So it's your turn