We've all got our anecdotes, of course, but I every time I replaced a CTE, cursor, or temp table sproc with an easier to read Merge statement performance skyrocketed. It wasn't even a fair challenge because all the CTEs and cursors were written WITH (NO LOCK) because who needs consistency or transactions, but yet Merge was faster with locks and transactions than CTEs avoiding them (and kowtowing to the local "we use NO LOCK in these parts" culture left them nearly "instantaneous").

Obviously, you have to watch your execution plans and make sure everything is indexed accordingly, because the guts of almost every Merge are a JOIN and if your database isn't set up for performant joins then of course you will hate Merge's performance.

And again, certainly anecdotal and a relatively long time ago in this industry's terms that I did any of that. But still something I'm fascinated by.

[ETA: Also, yes Merge is overpowered and underperformant for single row upsert, but as soon as you need bulk upsert, chef's kiss.]