5

I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in SYS.QUERY_STORE_PLAN the last_force_failure_reason_desc is 'General_Failure'.

Does anyone know where I might find more information for reason 8695?

This is happening with compatibility level set to 2016 & 2017.

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58
Copyright (C) 2017 Microsoft Corporation Enterprise Edition:
Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

Detailed description of the scenario

The query is from an encrypted stored procedure. I enabled the query store while in compatibility mode 2014 to capture the SQL plans. When I switch compatibility mode to 2016 or 2017, I have several queries experience serious degradation in performance. I am attempting to address this by forcing the plan from the Query store for that was generated while in compatibility mode 2014 but failing with reason 8695.

The stored procedure is generated by the application and it does not compile (via application) if we add hint OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); however, this hint does resolve the performance issue when we run the SQL Statement outside of the stored procedure.

I have been able to force plans for other SQL statements. This is my first attempt to force a plan for a statement contained within an encrypted stored procedure.

This particular SQL statement is an insert with a complex select from statement. BEGIN TRAN....INSERT INTO...SELECT FROM.... The other statements I have forced plans on have been complex select statements but not inserts or from within encrypted stored procedures.

I welcome workarounds. I'd like to use compatibility mode 2016 or 2017 if possible, but the performance-degraded queries are creating challenges that will need to be resolved.

  • Does the application call the stored procedure with a schema specified? Are you able to change the login of the process that calls the stored procedure? – Joe Obbish Feb 13 at 1:47
  • 2
    Are you able to share the execution plan for the query that is failing to force? You mentioned it includes an INSERT INTO...SELECT. There are some documented plan forcing limitations that you might be hitting. – Josh Darnell Feb 13 at 15:02

Your Answer

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

Browse other questions tagged or ask your own question.