SQL Plan Guides Use Same Plan Handle

Question

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

Answer 1

better off

If the difference between the two queries is something like:

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

vs.

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.