I’ve been reading Bob Ward’s book “SQL Server 2022 Revealed” and one of the new features in Intelligent Query Processing (IQP) jumped out at me. Not because it’s going to rock my world but rather it reminded me of an idea I had when I first started playing with Query Store when it came out.

I thought it would be cool if the execution plans cached in Query Store could be used to populate the plan cache after an instance restart, removing the need for all queries to be recompiled. I even raised a suggestion on Microsoft Connect for it to be considered as a new feature.

I won’t claim that anyone at Microsoft saw my post and thought “What a great idea!”, but by coincidence they have implemented in SQL 2022 something similar that works in specific scenarios – Optimized Plan Forcing.

The underlying idea behind it is to store – in query store – something called a compilation script that can be used to shorten significantly the compile time for a given query. Any query that runs when query store is enabled will get a compilation script stored if it has a significant compile time (the actual threshold is not disclosed). We can see can see which queries have compilation scripts by looking at the has_compile_replay_script in the sys.query_store_plan view.

If the query plan is forced in Query Store then the next time it needs to be compiled the optimization script will be used to build the plan instead of going through the usual query optimizer , greatly reducing the compile time.

Of course this only works for forced plans, but that makes sense as for other queries we often want a recompile to come up with a new and hopefully better plan. Still it’s an interesting feature. Bob talks about how it was designed in response to a pattern of performance problems they were calling a compile storm. This would be where the plan cache was emptied and then a lot of queries would have to be compiled concurrently causing CPU spikes. By shortening the compile time for some queries – and of course we DBAs can use Query Store to force as many plans as we like (with caution) it’s hoped to reduce the occurrence of such events.

I’m not going to go into trying to demonstrate this in action, but Bob has a full demo in his book which you can buy here:

The feature is turned on by default for databases on SQL Server 2022 as long as they have query store enabled – even if they’re not on the latest compatibility level. You can however turn it off for a given database using:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;

You can also turn it off at the level of an individual query. Either using the query hint DISABLE_OPTIMIZED_PLAN_FORCING, or if you force the plan using the sp.query_store_force_plan system stored procedure then you can disable it using the @disable_optimized_plan_forcing parameter.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.