Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when compatibility level changes to 2014. When I revert back to 2008, everything is dandy, then slow when I switch to 2014.
I want to know how I can force the plan (or how to in general force the SP) to run in the same way it did in 2008 CL (whether that's accomplished using Execution Plan forcing or not, doesn't matter to me).
Here is what I tried: I tried to FORCE execution plan using Query Store but that does not work. Because the SP generates a new QUERY ID every single time.. i don't understand how this tool is supposed to work, because no matter how much you force it, it'll be a new QUERY ID and therefore plan the next time. Here is what I did:
I can also confirm that this does NOT apply to me: Query Store Force Plan feature doesn't work