Hi. I have a query that returns few rows (out of 7 table join, tables have milions of records).
On our server it's fast, takes 46ms to complete. On client's server it takes 25-60 sec. The execution plan is similar in relations and indexes used, but almost every node has info like this:
3533121 of 26
while on our server it's normal
1 of 26 (4%)
Our server is on premise, while client's is in cloud, if it matters.
This craziness starts with "distinct sort" node. Like it rematerialized every row while accessing rows for sort. Out of memory? out of tempdb space?