Query Store And Better Plans

Grant Fritchey asks, what happens when you force a Query Store plan and later a better plan becomes available?

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.

Read on for the full answer.

Related Posts

Units Of Measure In The ShowPlan Schema

Grant Fritchey shows off the ShowPlan Schema: Because the showplan schema contains notes throughout stating what the units of measure are, what each of the values means. For instance, I can explain why SerialDesiredMemory, DesiredMemory, RequestedMemory are identical: …Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are […]

Read More

Good Query Store Default Settings

Erin Stellato gives us a starting point for good values for Query Store settings: QUERY_CAPTURE_MODE The default value for SQL Server 2016 and SQL Server 2017 is ALL.  For Azure SQL Database, the default value is AUTO. With AUTO, queries that are insignificant from a resource utilization perspective, or executed infrequently, are not captured.  If […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930