Thanks a lot for suggestions

Role and permission of dba in this project allow to use Query store (no extended events)

Dba might suggest and if needed create new indexes (preferably...

correct - it is fragile. and very frustrating - but with the correct usage and a DBA checking up on it all the time it can be very useful....

In order to measure the performance of the query, you'll need all 150 parameters (or however many are required and don't have defaults). There's no escaping this.

So, probably, best bet...

Have you looked at the error logs specified in this error message to find additional information?

1. OPENQUERY is your friend. Linked servers and letting SQL Server figure out if, maybe, this time, it pushes predicates to the second server to filter the data there...

I've found that Full Backups are taken through a snapshot of the VM itself within Azure and in return it breaks the log chain. That was what started my...

Thanks Grant/Jeff, that is exactly my methodology as well that I just wanted to confirm.

What do you both do surrounding the VM backup, because if you have the "SQL...

Whether running in a VM on Azure, AWS, or locally, a VM is just a VM. You're responsible for your database backups the same way across all of them. The...

I think the topic has strayed from the original question so it may be worth making two points about customer identifiers. Firstly, IDENTITY is likely not very convenient as...

OK, it's not doing the sort operation, but has the query sped up? I'm not comfortable with this solution. The problem you are having is not the sort operation. The...

You could. Depending on what you're doing, that's a lot of data movement

Well, in the two plans you showed, the sort was happening in both, just that one was only moving 9 rows. Is there a different plan?

Thanks for posting those.

The test system is moving 9 rows on an estate of 1.3 million. Production is moving 6.7 million rows on an estimate of 9.3 million. I think,...

They never reported back what the issue was. Usually, it's down to data.

Very good point, as usual.