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.

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.

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

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).

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;
GO

Here’s our failed friend:

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:

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:

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.