NO_PLAN and NO_INDEX: Breaking a Forced Query Store Plan

Whenever you’ve got a new feature, one of the first things to ask is, “What happens when I break it?”

Because we’re going to break stuff.

With 2016 Query Store, a natural question is, “What happens if I force an execution plan, and that plan is no longer valid?” We’ll take a tour in some detail, and then I’ll sum up all the takeaways in a nice little list at the bottom of the post.

First, a query with two plans

I’ve got a stored procedure, dbo.NameCountByGender. You give it a first name, and it returns a count of people with that name grouped by Gender.

I turned on Query Store, and it observed that I’ve had two different execution plans for the NameCountByGender procedure. Plan 2 has a higher average duration than plan 1.

Query-Store-Query-1-Has-Two-Plans-Avg-Duration

I use comic sans for Query Store because it’s a birthday party for execution plans.

Plan 2 (the purple dot) has an average duration of 3 seconds, conveniently noted as 3000000 microseconds. Plan 1 has an average duration of … a lot less. After squinting I think that’s 500 milliseconds.

Brief aside: are commas really expensive? Or are they just not cool?

Now let’s force Plan 2 using Query Store

Query Store is all about bossing things around. As soon as we click on that blue dot, it’s right there with a ‘Force Plan’ button.

Query-Plan-Force-Plan

I click it, and it makes sure I really meant to do that. Which is a good thing, because I click a lot.

Query-Store-Force-Plan-Confirmation

I tell it to force the plan, and we’re in business. That nested loop plan is here to stay!

Until I break it. You see how it’s doing a nice index seek at the top of the plan? We’re going to change that index.

What happens when we change an index used by a forced plan?

I’m going to leave the index in place, with its current name, and just add a column to it. A column that, not coincidentally, could actually be used by our query to avoid that key lookup.

Here’s the code I use– it’s just CREATE INDEX WITH DROP_EXISTING=ON

CREATE NONCLUSTERED INDEX ix_FirstNameByYear_FirstName ON 
    dbo.FirstNameByBirthDate_1980_1989 (FirstName ASC) INCLUDE (Gender)
    WITH (DROP_EXISTING=ON);
GO

After modifying the index, something really cool happens.

My stored procedure still works. It doesn’t fail! It can’t use the frozen query plan, so it generates a new plan (and actually figures out it can use the modified index better).

Query-Store-Query-Works-After-Index-Changed

Forced failure reason: NO_PLAN

You can query all your forced plans at any time, and figure out if and why any are failing with this query (you might want to adjust the timezones)

SELECT 
    qsp.plan_id, 
    qsp.query_id, 
    OBJECT_NAME(qsq.object_id) as containing_object_name,
    qsqt.query_sql_text,
    force_failure_count, 
    last_force_failure_reason_desc,
    qsp.count_compiles,
    qsp.last_compile_start_time AT TIME ZONE 'Pacific Standard Time' AS last_compile_PST,
    qsp.last_execution_time AT TIME ZONE 'Pacific Standard Time' AS last_execution_PST,
    qsp.avg_compile_duration/1000. as avg_compile_milliseconds
FROM sys.query_store_plan AS qsp
LEFT JOIN sys.query_store_query AS qsq on qsp.query_id = qsq.query_id
LEFT JOIN sys.query_store_query_text AS qsqt on qsq.query_text_id=qsqt.query_text_id
WHERE is_forced_plan = 1;

Here’s our failed friend:

Query-Store-No-Plan

What if we change the index definition back to how it was originally?

I revert my change with the following code. This just removes the included column and puts the index back to where it was when the plan was forced.

CREATE NONCLUSTERED INDEX ix_FirstNameByYear_FirstName ON 
    dbo.FirstNameByBirthDate_1980_1989 (FirstName ASC)
    WITH (DROP_EXISTING=ON);
GO

You guys aren’t going to believe this. IT WORKS AGAIN. I was so surprised, I tested it multiple times. The plan is sucessfully forced if you revert the index back to the old definition. Here’s confirmation in the execution plan properties that the plan was forced:

Execution-Plan-Properties-Use-Plan

If you drop an index, you’ll see failure reason NO_INDEX

While the index is dropped, the plan fails to be forced, but you do get an alternate last_force_failure_reason_desc of “NO_INDEX”.

If you create the index again with the correct definition, the plan can be successfully forced again. Here’s what a forced plan looks like in the Query Store Top resource consumers report:

Forced-Plan-Check-Mark

Takeaway: failure isn’t forever in Query Store, and it’s not too hard to decode

Pulling this all together, here’s the facts as I see them in 2016 RC1:

  1. If you use Query Store to force plans, you should periodically review which queries are forced, and if it’s still working.
  2. If plan forcing fails, the query still runs. It just doesn’t force the plan and will optimize fresh.
  3. If plan forcing is failing,  use the codes in last_forced_failure_reason_desc to review why. NO_PLAN indicates that schema has probably changed, while NO_INDEX means an index was probably dropped. For other codes, see sys.query_store_plan.
  4. If an index was dropped or changed temporarily, plan forcing should resume if it’s reverted to the previous state. This fact is good news for environments where periodic change release or batch processing requires different indexes.
  5. Just because you see values in force_failure_count and last_force_failure_reason_desc, that does NOT mean that the query is not currently forced. Those values don’t get cleared when plan forcing starts working again. That just tells you it has failed in the past, you still need to dig in and see what’s going on right now.
    • There are columns for the last compile time and the last execution time, but I have not found a column for the last failure time. Hey, not everything is easy.

 

 

 

Previous Post
3 Tricks with STATISTICS IO and STATISTICS TIME in SQL Server
Next Post
Training Plan for Junior DBAs Learning SQL Server

Related Posts

3 Comments. Leave new

  • Thanks for the post.I was testing about causes of forced plan failures.I found NO_Index as failure reasons in my query store but we don’t rebuild index daily but we have update statistics in place which runs after every 4 hours.

    And what I found is if your data in table has changed and you run update statistics which actually drops an execution plan or if you recompile your procs then you will see NO_INDEX as last_force_failure_reason_desc.

    so it is not just rebuild index but recompile and update statistics as well which may cause SQL to use different plan even though you have forced certain plan in query store.

    Reply
    • What you are describing is consistent with the behavior I’ve seen for automatically tuned/ autoforced plans, but NOT consistent with what I have seen and tested for manually forced plan. Are you referencing a system using automatic tuning, also known as automatic plan correction?

      Reply
  • Disregard my previous post and what we found is we are using user-defined table with primary key in our stored procedure.When I checked execution plan SQL scans data from user-defined table and gives a #key value for the primary key column.And whenever we update stats or recompile proc it drops that #key value for primary key column and creates the new one.That is the reason why we get No_Index as forced failures everytime we update statistics or recompile procs.
    I dropped the primary key and created index on user-defined table and now we don’t see any No_Index failures.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu