Without more information, hard to say. Since it looks like you can't provide much it may not be worthwhile to ask to see the execution plan or the definitions of the indexes on the tables. Both of these would be a good place to start your research. Just a guess, you don't have indexes that maybe beneficial to your queries. Then again, I could be wrong.
No problem, the issue is the query when moved to our UAT environment from DEV and SIT quits working correctly when using Subqueries. Any idea why this behavior would change. I noted the extra details of DEV/SIT are virtual and UAT is clustered ACTIVE/PASSIVE Cluster. And the idea it could be a size driven issue does not seem to be the case.
Structures are exactly the same between DEV, SIT and UAT. The query is self blocking as best we can tell but use of NO LOCK and TRANSACTION ISOLATION LEVEL options do not fix it, nor does it show up in blocking list.
Yes. When was the last time you rebuilt statistics on the slower box?