So, performance comes down to identical behaviors. A similar query running on a similar structure with similar data sets may, yeah, perform wildly different. This is because every little thing...

Oh yeah, take a look at the execution plan. You can just get an estimated plan, you don't have to execute the query. That will show you all the activity...

Almost impossible to tell you exactly what is wrong without more data. It could be as simple as you didn't commit your transaction. It could be blocking. Assuming you have...

It's simple. COALESCE as part of filtering criteria, WHERE/ON/HAVING, means that statistics and indexes can't be used becuase you're making a calculation. That's going to slow things down, leading to...

It does. It's just not obvious when looking at the XML. You can open the blocked process report as a report which will resolve the values for you. However, to...

I once built a system that could query for query text and then execute that. It was a way to intentionally allow people to inject SQL code into the system...

It's going to be the same as the other stuff we've been talking about. You have to capture metrics that show where the slow points are. Extended Events are your...

It's going to be the same as the other stuff we've been talking about. You have to capture metrics that show where the slow points are. Extended Events are your...

You don't want any one session to do too much. It makes it harder to distinguish the information, search for what you want, etc.. Plus, you put more of a...

Oof. Trace. Not extended events. These are even more resource intensive than extended events. I'd turn them ALL off and go with XE, especially because your system is already under...

Is the blocked process report running as part of query collection? It shouldn't separate those out. One as one session. One as another. And, yeah, capturing query metrics, especially at...

I don't have one I can recommend,...

Have you captured the blocked process report? That's going to show you the precise blocking chain. You'll have a total story, and one that cannot be argued with. And yeah,...

A bunch to unpack here. Let's start with the nerdier stuff.

You list a bunch of events for capturing query metrics, but, you're missing a few, sp_statement_completed for example, since you're...

Personally, I'd go with what I'm familiar with and can support. If you're better in AWS, that's probably your best bet.

HOWEVER...

I would also be very cautious about moving your friend...