Query Store, Force Plan, and ''Better'' Plans
Query Store, Force Plan, and ''Better'' Plans
Will plan forcing in the Query Store pick up changes once the physical structure of a database changes? Read on to find out more.
Join the DZone community and get the full member experience.Join For Free
Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.
I am endlessly fascinated by how the Query Store works. I love teaching it at every opportunity, too. Plus, almost every time I teach it, I get a new question about the behavior that makes me delve into the Query Store just a little bit more, enabling me to better understand how it works. I received just such a question at SQLSaturday Norway:
If you are forcing a plan, and the physical structure changes such that a “better” plan is possible, what happens with plan forcing?
Let’s answer a different question first. What happens when the plan gets invalidated, when the index being used gets dropped or some other structural change occurs so that the plan is no longer valid? I answered that question in this blog post. The plan being forced, after the object is dropped, becomes invalid, so that plan can no longer be used. The Query Store still attempts to apply the plan during any recompile or compile event of the query in question, but it fails and a proper plan is used. All this means, I think, the Query Store is going to ignore the new index, since a new index doesn’t invalidate an existing plan. A new index just makes new plans possible. However, when I was asked this question, this wasn’t something I had tested, so I gave a speculative, best guess, answer with plenty of caveats and the promise to provide a tested answer ASAP. Here we go.
I’ll start with the same sample query:
SELECT sit.Quantity, sit.TransactionOccurredWhen, i.InvoiceDate, si.StockItemName FROM Warehouse.StockItemTransactions AS sit JOIN Sales.Invoices AS i ON i.InvoiceID = sit.InvoiceID JOIN Warehouse.StockItems AS si ON si.StockItemID = sit.StockItemID WHERE sit.TransactionOccurredWhen BETWEEN '3/1/2015' AND '3/5/2015';
The results are returned in about 53ms with 4,850 reads and this execution plan:
As you can see, there’s the suggestion of a possible missing index. We’ll apply that in a moment. First though, I’m going to get the plan and query identifiers from the Query Store:
SELECT qsq.query_id, qsp.plan_id, CAST(qsp.query_plan AS XML) FROM sys.query_store_query AS qsq JOIN sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id WHERE qsqt.query_sql_text LIKE 'SELECT sit.Quantity, sit.TransactionOccurredWhen,%';
With this information, I’ll use Force Plan to ensure this is the plan used going forward.
EXEC sys.sp_query_store_force_plan 42995,487;
With that done, I’ll create the index:
CREATE INDEX TransactionOccurredWhen ON Warehouse.StockItemTransactions (TransactionOccurredWhen) INCLUDE (StockItemID,InvoiceID,Quantity);
When I do this same set of operations, run the query, identify a missing index, create a new index, rerun the query, when there is no plan forcing occurring, the execution plan above is replaced with one that uses the index and results in about 32ms execution time with 2942 reads, a significant improvement. You get a recompile event because the schema involved with the query has changed. With the change, a new index is available, so the recompile event uses that new index. What happens when you force the plan?
The recompile event after running CREATE INDEX still occurs. However, because we have elected to force a plan, that plan is what is used. In this instance, a recompile to a new plan would result in a faster query using fewer resources. However, as long as we’re forcing a plan and that plan stays valid, the plan will be forced.
In short, the behavior is exactly as I expected. Choosing to force a plan in the Query Store results in that plan being forced. While I think that the Query Store and plan forcing are wonderful new tools in our tool box, I am concerned that plan forcing will become far too easy a thing to implement. I worry that people will implement it without thinking through the implications and potential impacts.
It gets worse. If I change the query, let’s say I make it into a stored procedure and parameterize the query, and, instead of a very limited date range, I send in a whole month, the execution plan is quite different (with or without the index). Forcing the plan that is expecting less than 1,000 rows onto a query that is retrieving 10,000 rows results in pretty horrific performance. We really are going to have to be careful about using plan forcing appropriately because, as in so much of the rest of SQL Server, and in all of programming for that matter, the code is going to do exactly what we tell it do, whether that’s what we really want it to do or not.
Published at DZone with permission of Grant Fritchey , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.