add RECOMPILE for specific query server wide

Question

I've SQL 2017 and i have a query performance problem. i did try to play with all those trace flag (4199,4136,4137,9481)server wide and play with the indexes and stats and the query is still running slow. the only thing that resolve the issue is if i add the OPTION (RECOMPILE) to the query. my problem is that i can't add it to query itself that comes from the ERP program.can i use it server wide for specific query?

Answer 1

You can use a plan guide. What type of plan guide to use depends on whether the query is in a proc (OBJECT) or not (SQL). Example:

sp_create_plan_guide   
@name = N'myGuide',   
@stmt = N'SELECT col1, col2 FROM MyTable',  
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (RECOMPILE)'

Documentation for plan guides here: https://learn.microsoft.com/en-us/sql/relational-databases/performance/plan-guides