Sql-server – SQL Server Query Store – Force Plan does not work

optimizationsql serversql-server-2016

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:
enter image description here

I can also confirm that this does NOT apply to me: Query Store Force Plan feature doesn't work

Best Answer

Query store uses the query hash to identify and group queries together. If you're seeing a new plan ID every time this proc executes, it could be because you have a different SQL hash each time maybe because of dynamic SQL. Without seeing the code it is difficult to determine why.

You should check your cache for recent executions of this procedure and verify if they have different query hash values as this will be impacting Query Store's ability to track that query's executions.

To force the old Cardinality Estimator, you can add OPTION(QUERYTRACEON 9481) to your queries to force the use of the old pre-2014 cardinality estimator. In SQL 2016 SP1 and higher, you can also use OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) which does the same thing but doesn't require permissions to turn on trace flags.

This should force the use of the old CE and return your query to more predictable performance levels, however, you should also look at tuning and refactoring your stored procedure to identify the cause of the poor performance with the 2014+ CE and resolve it if possible.

Related Question