0

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).

  • I mean, all kinds of reasons the same query could give different performance on different servers, usually statistics play a role if you've ruled out most else. I wrote a post about this: Different Plans for "Identical" Servers. Also, is there a reason you are staying on 2012 compat mode? – Aaron Bertrand Apr 25 '18 at 22:14
  • So you're saying the plan executes differently but it's the same plan? Can you share them? It's really tough to confirm your theory without queries or plans, so I'm not sure what you're looking for. The link I posted above has a collection of other possible reasons for differences across servers, but you seem to think these are the same plans that work differently, which doesn't make sense to me at a high level. Also if you know the specific queries that cause problems in the new compat, and you've tried all other avenues of tuning them, you could hint those queries explicitly. – Aaron Bertrand Apr 25 '18 at 23:16
  • 1
    Can you share both plans (or all 4) instead of just one? Preferably, capturing actual execution plans using SentryOne Plan Explorer? We can't troubleshoot what we can't see and you're only willing to wave your hands about. – Aaron Bertrand Apr 26 '18 at 0:21
  • 1
    Sure, I've added them to the link above. Sorry, I was not trying to be difficult, but I felt that my question had a sufficient amount of information to explain the problem enough that if this was a known issue someone could tell me that. Hopefully with the plans you can better see the problem. – PaulM Apr 26 '18 at 10:11
1

The big difference in the serial plan that I can see is this expensive TOP operator that has been introduced by the optimizer:

enter image description here

The TOP is probably there due to row goals, there is a 4-part series on this at SQLPerformance.com:

(Part 1 and maybe part 2 are relevant for this query.)

I can't confirm for sure in your case because you're using 2016 and the showplan properties that expose row goal information weren't added until 2017 CU3 (and were added in 2016 SP2, just released this week). You can see them if you show the output from running the original, serial query on the server adding the following (taken from part 1 of the series above):

OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607, QUERYTRACEON 8612);

Estimates are different all over the place, so that tells me that data and/or stats are different, but probably mostly stats since local and server both give the same actual rows.

I suggest a rewrite, changing IN to EXISTS, simplifying the subquery (a lot of redundant work there), and removing the GROUP BY (which is unnecessary busy work, and may be the culprit here):

SELECT p.id_prov,
       p.name,
       ps.val
FROM dbo.provider_payment pp
INNER JOIN dbo.provider p 
  ON p.id_prov = pp.id_prov
INNER JOIN dbo.payment_settings ps 
  ON ps.id_prov_payment = pp.id_prov_payment
WHERE pp.payment_method = 'PROTX'
  AND ps.setting = 'VendorName'
  AND p.is_deleted = 0
  AND p.status = 'Y'
  AND EXISTS
  (
    SELECT 1
      FROM dbo.deposit_payment dp
      WHERE dp.id_prov = p.id_prov
        AND dp.payment_method = pp.payment_method
        AND dp.create_date > '2018-01-01'
  );

Updating stats can't hurt, but let us know if the rewrite helps. You could also try hinting the newer cardinality estimator using OPTION (QUERYTRACEON 2312); and in case you are hit by anything that has since been fixed in the optimizer you can also try 4199.

Leaving the local variance out, the two plans on the server have the same predicate on the scan of deposit_payment, except that the parallel plan uses a probe for id_prov. So I really don't think the predicate has anything to do with any performance differences:

enter image description here

The only difference between server and local is that the local serial plan handles the id_prov filter later, in a hash match. Exercise to the reader to determine if that's better or worse. :-)

I would focus on writing simpler queries without unnecessary and/or redundant logic (and perhaps introducing a supporting index on deposit_payment to help this type of query without a scan/predicate) rather than trying to discover bugs. :-)

  • Thanks Aaron. Rewriting it does fix it, but I was just wondering if the predicate applied to the clustered index scan on the deposit payment table is a mistake? It only appears in 1 of the plans and is referencing the outer query when it shouldn't. I guess my concern was more around whether or not this is some sort of bug to watch out for, as I haven't seen that happen before. – PaulM Apr 26 '18 at 15:55
  • 1
    Your local serial plan is the only one that has a different predicate (the id_prov filter happens later, in a hash match), the other three have pushed the id_prov match down into the scan. Again, this could be due to row goals, I'm not 100% certain. The two plans on the server have the same predicate except the parallel plan expresses the id_prov as a probe. I would strive to write simpler queries, making the optimizer not work as hard. It's not perfect (though without more info, calling this a bug might be a stretch). – Aaron Bertrand Apr 26 '18 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.