Why won't SQL Server 2012 use my "better" execution plan when provided via the USE PLAN hint or through a stored query plan?

Question

I have a 3rd party application that I cannot change that runs a "bad" select query that can run very slowly in some circumstances (we have killed it after 6 hrs on occasion). This is after we've done what we could to speed it up via indexing, which is something we can control.

    SELECT *
    FROM findetail 
    WHERE fintransact is null 
    and (validation is null or validation <= ' ' or validation = ' Zero value transaction;') 
    and (exists (select * 
                 from counterparty 
                 where counterparty.counterparty = findetail.counterparty 
                 and counterparty.status = 'ACTIVE')  
          or findetail.counterparty is null) 
    and (findetail.acctstatus = 'FINAL' or findetail.acctstatus = 'PROVISIONAL') 
    and (findetail.transactiontype = 'AP' or findetail.transactiontype = 'AR') 
    and ((findetail.position not in (select position.position 
                                     from position 
                                     where position.positionmode = 'FINANCIAL' 
                                     and position.exchange is not null)
                                     ) 
          or (findetail.fee in (select fee 
                                from fee, feetype 
                                where findetail.fee = fee.fee 
                                and fee.feetype = feetype.feetype 
                                and feetype.excludemargin = 1 )
             )
       )
    and ( findetail.counterparty = 'ACPTY' )

It is easy to re-write the query to be at least somewhat "better":

SELECT *
FROM findetail 
WHERE fintransact is null 
and (validation is null or validation <= ' ' or validation = ' Zero value transaction;') 
and (exists (select * 
             from counterparty 
             where counterparty.counterparty = findetail.counterparty 
             and counterparty.status = 'ACTIVE')  
       or findetail.counterparty is null) 
and (findetail.acctstatus = 'FINAL' or findetail.acctstatus = 'PROVISIONAL') 
and (findetail.transactiontype = 'AP' or findetail.transactiontype = 'AR') 
and (not exists (select position.position 
                 from position 
                 where position.positionmode = 'FINANCIAL' 
                 and position.exchange is not null
                 and position = findetail.position)
      or exists (select fee 
                 from fee, feetype 
                 where findetail.fee = fee.fee 
                 and fee.feetype = feetype.feetype 
                 and feetype.excludemargin = 1 
                 and findetail.fee = fee)
   )
AND ( findetail.counterparty = 'ACPTY' )

This one is still not perfectly optimal but runs in about 8s which is much preferable to multiple hours. It also keeps the rough structural format of the original which is important because a last-ditch option is to try and edit the query string (which is visible but broken into sections as it’s dynamically built up using some “if” statements in the code) in one of the third party application’s .dll’s.

Aside: When I completely re-wrote the query to be "even better" (down to 3s runtime) using proper joins, SQL server would no longer recognize this "even better" execution plan as valid for the "bad" query when I tried to use it via USE PLAN query hint. Plus, it wouldn’t be possible to edit that one back into the .dll if I end up trying that.

So I captured the execution plan of the "better" query, and tried to force it onto the "bad" query using the USE PLAN hint. SQL Server accepts the forced plan as valid (at least, doesn't complain like it did for the "even better" plan), but it also doesn't actually use the "better" plan. Show estimated execution plan shows the same bad plan as before, and actually executing results in the query running for at least 10 minutes before I killed it.

I've tried this in SSMS with both a USE PLAN hint at the end, and through creating a stored query plan. Either way, SQL Server when the query is run continues to use the "bad" plan. Any ideas on why if it thinks the plan is valid it’s still not using it?

Any help is appreciated, Thanks!

Answer 1

For the purposes of answering, let's label these as Query A and Query B. Query A and Query B are not identical (even if they produce equivalent results).

You cannot force SQL to use a plan generated for Query B when asking it to execute Query A.

USE PLAN is for running a specific plan when multiple plans exist for a single query. https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/

Answer 2

Rob, here is the plan from sentryone plan explorer: