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

  • It would be very helpful if you could show the query code, along with the table definitions, as shown here. Additionally, you could add the actual execution plans for the query in the old compatibility level and the new compatibility level. brentozar.com/pastetheplan works well for execution plans. – Max Vernon Mar 18 '19 at 17:08
  • I am sure Problem is in configuration of Sql server 2014.Using force Plan is bad bad idea. – KumarHarsh May 2 '19 at 4:12
  • @KumarHarsh I am not sure why you think it's a bad idea: it is the ONLY supported way and it's the ONLY way provided by Microsoft. Have a look here: docs.microsoft.com/en-us/sql/relational-databases/performance/… – LearnByReading May 2 '19 at 14:35
  • @KumarHarsh I agree with you that it's inconvenient, counter-intuitive and inefficient, but I don't know what else can be done - it's the official way and while I think it's silly to do it, I cannot think of any other solution unless you go back and rewrite and recreate hundreds of queries that have the issue. And the problem is not configuration related, it's an expected behaviour – LearnByReading May 2 '19 at 14:37
  • Your problem is that one query working fine in 2008 but not working in 2014.So I am telling you to avoid Force Query Plan,rather look for configuration and compatibility level.Actually I don't remember the exact configuration thing to check, but We have solve same problem in Sql server 2016 in this forum only. – KumarHarsh May 3 '19 at 3:27

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.

  • Thank you for the answer. However, I don't believe CARDINALITY_ESTIMATION is the only cause of the issue. Other factors, like Query Optimizer behaviour could be (and I believe are likely) causing the problem. Also, my code does not generate/use dynamic SQL. I will try your solution however. Thank you. My code is very simple, just a bunch of joins (inner/outer/left/right). Nothing beyond joins and a lot use of #TEMP tables – LearnByReading Mar 18 '19 at 4:03
  • @LearnByReading did you try the hint suggested in this answer? Did it help at all? – Max Vernon Mar 18 '19 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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