I have a query which contains a subquery. When executed on a local copy of my database it runs as normal and executes within < 1 second.
I've noticed however that if I run this same query on our server the inner query behaves like a correlated subquery, taking around 90 seconds, and the predicate applied to a clustered index scan for data which relates to the subquery appears to be incorrect - it contains the alias used in the outer query, even though all aliases used in the inner query are different, and instead of 1 execution for the index scan it shows hundreds.
I've noticed that this behaviour only applies when the query executes serially - if I lower the cost threshold for parallelism
on the server and it goes parallel then this behaviour does not appear and the query runs correctly. Notably, the query runs correctly in my local environment whether serial or parallel, and the predicate applied there looks fine.
Both the local environment and the server are running SQL Server 2016 SP1 CU7 (Enterprise on the server, Developer locally), and I've looked at the different SET
options and verified that they are using the same compatibility level (SQL 2012).
Is there anything that would explain the difference of behaviour here?
The problem is not that the plan is different, it's that it's not a correlated subquery and is being executed as one - the inner query is incorrectly referencing the outer query when the query executes serially, but not when it goes parallel on the same server.
The server is set to 2012 compatibility mode because when we upgraded, the new cardinality estimator caused some problems with a few queries and we haven't gotten around to fixing those yet so have remained on 2012 compatibility mode to use the legacy cardinality estimator.
All 4 plans can be viewed here.
Looking at the query and then at the clustered index scan on the deposit payment table, this shouldn't be using the predicate it is using as it references the outer query, which unless I'm misunderstanding, is incorrect. It also shouldn't be executing that operator the 200+ times that it is.
On the same server, if I lower the cost threshold for parallelism
so that it goes parallel, it applies a different predicate to that operator where it does not reference the outer table and does not behave like a correlated subquery. This is the same behaviour I see if I run the query locally (either running serially or parallel).