I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in
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.