I have a few databases with identical schemas, let's call them:
I have a query that takes 11 seconds to execute and returns 11,000 records in DB_A. However, if exactly the same query is executed in the same DB_B, it takes 40 seconds and returns 7,000 records.
The schema is identical, so the query is, but when I run it on DB_B, it goes to degree of parallelism 1, on DB_A it goes to 16.
I tried to set the threshold cost to 0 to enforce concurrency, but I got the same result.
Why is that? How can the same query that is executed in cloned databases behave so differently?
Any ideas are welcome.
I use SQL 2017 standard.