Have a table that is written to a lot but the data is then removed by the app side and stays relatively constant. It is hit by multiple queries that are the same except for the IN clause, there can be one, two or 50 options

WHERE Name IN ('test1', 'test2', 'test3', 'etc', 'etc') 

The code is from within the app and cannot be altered, coming from Entity framework or something similar. I have improved several queries by using plan guides, I am aware these can create tech debt as a good plan today may not be a good plan tomorrow but as this table is written / cleared regularly we haven't experienced this.

So my question, I have two queries that only differ by 2/3 of the IN clause values, I have locked in a good plan using a guide for query1 is it possible to lock the same plan handle against query2. The plans are similar the 'good' and 'bad' plans only difference is a sort operation.

Running SQL 2017 Ent

1 Answer 1


better off

If the difference between the two queries is something like:

WHERE Name IN ('test1', 'test2', 'test3', 'etc1', 'etc2') 


WHERE Name IN ('test1', 'test2', 'test3', 'etc3', 'etc4') 

Then you are probably in a good position to approach things differently.

  1. Enable Forced Parameterization
  2. Use Query Store to force plans

Forced parameterization should get you literal value replacement so that both queries match across hashes, which would also give you the ability to force a single good plan for them of your choosing.

  • As always, thanks Erik We already have forced parameterization switched on for this DB and, we did try and use query store but it just couldn't cope, think old Siebel environment It has started to behave itself now, the table had massive growth due to app side issues
    – Stockburn
    Feb 15 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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