or
Josh Darnell
sql-server sql-server-2019
**tl;dr:**   
What characteristics lead to an execution plan benefiting from accelerated plan forcing?  Are there any situations where accelerated plan forcing should be disabled (in practice or in theory)?

---

SQL Server 2019 introduced a new database scoped configuration option called `ACCELERATED_PLAN_FORCING` (APF).  Little has been said about it in terms of announcements, although it is [documented][1]:

> Enables an optimized mechanism for query plan forcing, applicable to all forms of plan forcing, such as Query Store Force Plan, Automatic Tuning, or the USE PLAN query hint. The default is ON.

It's also notable in that it is on by default, which implies to me that the SQL Server team think there is little downside to using the feature.  In fact, the docs even go on to warn against turning it off:

> ❕ Note
> 
> It is not recommended to disable accelerated plan forcing.

In pursuit of seeing the feature in action, I found a big AdventureWorks2014 query [online][2], and started adding more joins and subqueries to it.  It looks like this:

```
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Sales.CreditCard cc ON cc.CreditCardID = soh.CreditCardID
INNER JOIN Sales.CurrencyRate cr ON cr.CurrencyRateID = soh.CurrencyRateID
LEFT JOIN Sales.Currency crn ON crn.CurrencyCode = cr.FromCurrencyCode and crn.ModifiedDate = cr.ModifiedDate
INNER JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID
LEFT JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID
INNER JOIN Sales.SalesTerritoryHistory sth ON st.TerritoryID = sth.TerritoryID and sth.StartDate = DATEADD(dd, st.SalesLastYear%10, GETDATE())
INNER JOIN Person.BusinessEntity be ON sp.BusinessEntityID = be.BusinessEntityID
INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = be.BusinessEntityID
INNER JOIN Person.[Address] a ON bea.AddressID = a.AddressID
LEFT JOIN Person.StateProvince spr ON a.StateProvinceID = spr.StateProvinceID
INNER JOIN 
(
	SELECT m = MAX(tha.TransactionDate), tha.ProductID 
	FROM Production.TransactionHistoryArchive tha 
	GROUP BY tha.ProductID
) tha ON tha.ProductID = sod.ProductID
INNER JOIN 
(
	SELECT spsbfy.SalesPersonID
	FROM Sales.vSalesPersonSalesByFiscalYears spsbfy
	WHERE 
		spsbfy.[2002] IS NOT NULL 
		AND spsbfy.[2003] IS NOT NULL 
		AND spsbfy.[2004] IS NULL
) pe ON pe.SalesPersonID = soh.SalesPersonID
WHERE
	cc.CardType IN ('SuperiorCard','Vista') AND
	p.PersonType = 'IN' AND
	(soh.SalesOrderNumber > 'SO' OR spr.CountryRegionCode IN ('FR', 'US'))
```

Getting an estimated plan for that query takes around 100-150 ms on my machine.  I then take the XML for that plan and paste it into an `OPTION (USE PLAN N'xml goes here')` query hint.  For space reasons, I won't include that in the post body, but you can grab it [here][3] if you're interested.

Getting an estimated plan with the hint in place, and APF disabled:

```
USE [AdventureWorks2014]
GO
ALTER DATABASE SCOPED CONFIGURATION SET ACCELERATED_PLAN_FORCING = OFF;
GO
```

Results in a compile time of 600-650 ms - 4x higher than the compile time without the hint.

Enabling the feature:

```
USE [AdventureWorks2014]
GO
ALTER DATABASE SCOPED CONFIGURATION SET ACCELERATED_PLAN_FORCING = ON;
GO
```

Causes compile time to drop back to 150 ms.  Clearly this query is a good candidate for the feature.

However, if I remove the last two `INNER JOIN`s on subqueries (aliased `tha` and `pe`), that plan does not benefit from APF at all (compile time of the forced plan is the same with APF on or off).  The query, including the `USE PLAN` hint, is [here][4] for convenience.

So why do the additional `INNER JOIN`s on subqueries make a difference in the effectiveness of this feature?  

Is it purely based on plan *size* (like, in KB)?  

Or is that just an indirect correlation, and the reason is more along number of joins / tables accessed, number of indexes to choose from, number of stats loaded, etc?

[1]: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15
[2]: https://www.sqlshack.com/query-plan-on-a-busy-server/
[3]: https://gist.github.com/jadarnel27/a67114b0fa01c423b33c19e238715244
[4]: https://gist.github.com/jadarnel27/8022dec1130222f2a7d5690ffacbef69
Top Answer
Paul White
As the documentation suggests, this feature should almost always be beneficial (or at least no worse) compared with the previous mechanism.

## Prior mechanism

Prior to SQL Server 2019 guided plan search was restricted to the *search2* phase of query optimization. This is also known as *full optimization*, where all exploration rules are available, and deeper search is enabled. Compilation was always *started* in *search2* rather than progressing from earlier stages.

This might seem sensible, but *search2* is normally tried last for good reasons. Having all exploration rules available means a large number of tasks can be generated, even with guided search active. The additional rules enabled in *search2* also tend to be more computationally expensive in themselves, as well as possibly generating a large number of new alternatives, which might themselves match again on expensive rules. Finally, the deeper search allows for alternatives generated from alternatives further than the *search1* distance limit of 5.

Examples rules applied only in *search2* include:

* `OJOJSwitch - (A OJ B) OJ C -> (A OJ C) OJ B`
* `ReorderLOJN - (A LOJ B) LOJ C -> A LOJ (B LOJ C)`

## New mechanism

This is able to apply guided search during *search1* (aka *quick plan*), which is where the majority of final plans are found anyway. If an equivalent to the guide cannot be found in *search1*, *search2* may be needed. When *search2* is needed, the time taken will usually be much less than under the old mechanism because the search is much more limited and targeted using the new arrangement. Note the database must be set to 150 compatibility to use APF.

Your example queries would both qualify for *search2* on complexity grounds, but the best cost plan found after *search0* and *search1* is low enough that entry to *search2* is not justified. More than that, the search even 'timed out' during *search1*. In other words, the optimizer stopped searching because a good enough cost plan was found.

Being able to apply guided search during *search1* is a win here because the cost of parsing and validating the XML showplan is more than repaid by finding a guided plan more quickly than via regular search.

The previous mechanism would have tried to find a matching plan via *search2*, which was frequently slower overall for the reasons mentioned previously.

### Monitoring

You can monitor the effectiveness of the new mechanism using the Debug channel extended event `quickstage_useplan_statistics`.

On my machine, the full query produced the following event data:

* compilecpu_time_ms = 224 (from the plan guide)
* fullopt_time_ms = 0 (we didn't need *search2*)
* quickstage_successful = True (we found a match in *search1*)
* quickstage_time_ms = 41 (New time)
* quickstage_useplan_enabled = True (config setting is on)
* useplan_successful = True (plan found)

Note the times do not account for the XML parsing and validation time. You can see this using undocumented trace flag 8675 (with 3604 or 3605 as usual), if you're interested. When guided search is active, this produces output starting with something like:

```none
USE PLAN hint parsing and validation time: 604 ms
```

### Test timings

The *estimated* execution plans showed 124ms for the unhinted full query and 142ms for the accelerated hinted query. Without acceleration, the guided plan took 774ms. The extended event shows most of this time was spent in *search2*:

* fullopt_time_ms = 666

For the query with the derived tables commented out:

* compilecpu_time_ms = 103
* fullopt_time_ms = 0
* quickstage_successful = True
* quickstage_time_ms = 25
* quickstage_useplan_enabled = True
* useplan_successful = True

The *estimated* execution plans showed 91ms for the unhinted cut-down query and 88ms for the accelerated hinted version. Without acceleration, the guided plan took 91ms. The extended event shows:

* fullopt_time_ms = 22

So it makes little difference either way for this query with a small database like AdventureWorks.

## Your question

> What characteristics lead to an execution plan benefiting from accelerated plan forcing?

This is really the wrong question, because you should only force a plan when you have no other choice e.g. natural optimization sometimes selects a terrible plan, and all other tuning efforts have failed. Remember you will need to monitor the forced plan as data volumes and distribution change over time, and when schema changes occur.

Anyway, that said:

If you do *need* to force a general plan shape, you should do no worse using the new mechanism than you did before. Sometimes it will be a big win. It is difficult to assess in advance because it depends how long the old mechanism would have spent in *search2*, and whether the new one can find a match during *search1*. Plan forcing will still sometimes be slower than compiling a fresh plan without guidance. Remember the overhead of XML parsing and validation. Still, the new system might make plan forcing to reduce compilation time more viable than it used to be.

Microsoft don't document the exploration rules that can only be applied in *search2* (and the details have changed over time). This makes it even harder to link to query features. At best, this would be a weak relationship because there is no reliable way to match declarative T-SQL with the state of the internal query tree at any point during the optimization process, or how far a particular optimization session might progress through the available stages.

To avoid being completely frustrating, I will say that complex aggregates (capable of being split or pulled/pushed around the tree) and multiple outer joins can lead to a lot of extra time being spent in *search2*. Queries with high estimated cost are also more likely to make it to full opt, and progress to deeper levels of recursive exploration.
What are the use-cases for ACCELERATED_PLAN_FORCING?
Paul White
(natural, unforced plan of course)
Paul White
All things being equal the query will now use batch mode on rowstore and search2
Paul White
Cool
i-one replying to Paul White
Oh! Yes, db compatibility was the issue. With 150 and `USE PLAN` optimization ended in S1 as well.
Paul White
SO2010 defaults to 100 compat IIRC.
Paul White
Check your database is 150 compat.
Paul White replying to i-one
Something isn't quite right there. With APF ON you would will see activity in S1. Going straight to S2 is a sign that APF is OFF.
i-one
2019 APF ON. Probably _"new arrangement"_?
Paul White replying to i-one
Is that on 2019 with APF ON or OFF?
i-one
> USE PLAN hint parsing and validation time: 8 ms   
> End of simplification, time: 0.001 net: 0.001 total: 0 net: 0.001   
> end exploration, tasks: 485 no total cost time: 0.017 net: 0.017 total: 0 net: 0.018   
> end search(2),  cost: 1624.16 tasks: 879 time: 0.001 net: 0.001 total: 0 net: 0.02   
> End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.02   
> End of query plan compilation, time: 0.001 net: 0.001 total: 0 net: 0.022   
i-one
then pasted obtained plan to `USE PLAN`
i-one
> End of simplification, time: 0 net: 0 total: 0 net: 0   
> end exploration, tasks: 467 no total cost time: 0.022 net: 0.022 total: 0 net: 0.022   
> end search(1),  cost: 1690.05 tasks: 2090 time: 0.005 net: 0.005 total: 0 net: 0.028   
> end exploration, tasks: 2091 Cost = 1690.05 time: 0 net: 0 total: 0 net: 0.028   
> end search(1),  cost: 1609.51 tasks: 4170 time: 0.01 net: 0.01 total: 0 net: 0.038   
> End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.039   
> End of query plan compilation, time: 0.001 net: 0.001 total: 0 net: 0.04 
i-one
Disabled s2 and took an estimated plan
i-one replying to Paul White
Damn it, can't reproduce, but I think I saw it. Following is reproducible on my machine though.
Paul White replying to i-one
Not *implementation* rules, no but you still need the right exploration rules to get a compatible shape.
Paul White replying to i-one
Uses s2 for me without `USE PLAN`
i-one replying to Paul White
Strangely that without `USE PLAN` optimization ends in search1 for this query, but with `USE PLAN` search2 is required.
i-one replying to Paul White
But what is the reason in this case, are there any implementation rules available in search2 only?
Paul White
:)
Josh Darnell replying to Paul White
What a nifty query!
Paul White
(with s2 disabled)
Paul White
```none  
USE PLAN hint parsing and validation time: 5 ms
End of simplification, time: 0.002 net: 0.002 total: 0 net: 0.002
end exploration, tasks: 869 no total cost time: 0.007 net: 0.007 total: 0 net: 0.01
end exploration, tasks: 870 no total cost time: 0 net: 0 total: 0 net: 0.01
end exploration, tasks: 897 no total cost time: 0 net: 0 total: 0 net: 0.01
end exploration, tasks: 900 no total cost time: 0 net: 0 total: 0 net: 0.01
end exploration, tasks: 928 no total cost time: 0 net: 0 total: 0 net: 0.01
end search(1),  cost: 1.79769e+308 tasks: 928 time: 0 net: 0 total: 0 net: 0.01

Msg 8698, Level 16, State 0, Line 3
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. 
Remove or replace USE PLAN hint. 
For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
```
Paul White
Obviously there was a `USE PLAN` hint on that.
Paul White
![stage2](/image?hash=2023fe0d469b8f8e869b0a02475c54f017e4e6a776cf80142b63a9846134a3ca)
Paul White
```sql  
SELECT 
    U.DisplayName, 
    PT.[Type], 
    (SELECT COUNT_BIG(*) FROM dbo.Posts AS P2 WHERE P2.Id = PL.RelatedPostId),
    (SELECT COUNT_BIG(*) FROM dbo.Comments AS C WHERE C.UserId = U.Id),
    (SELECT COUNT_BIG(*) FROM dbo.Votes AS V WHERE V.UserId = U.Id),
    (SELECT COUNT_BIG(*) FROM dbo.Badges AS B WHERE B.UserId = U.Id)
FROM dbo.Posts AS P
LEFT JOIN dbo.PostLinks AS PL ON PL.PostId = P.Id
LEFT JOIN dbo.Users AS U ON U.Id = P.OwnerUserId
JOIN dbo.PostTypes AS PT ON PT.Id = P.PostTypeId;
```
Paul White replying to i-one
Yep. This is using the StackOverflow2010 database:
Paul White
Let me see if I still have it.
Paul White
Yes I had one just yesterday.
i-one
I'm curious though, if it is possible to write such a query that can not be solved without entering _search2_, in this case disabling it would be just dangerous.
i-one
Oh, seems I overlooked _"more limited and targeted"_ piece.
Paul White
I can't think of a case where what you are asking would make sense
Paul White
As I say in the answer:  
> If an equivalent to the guide cannot be found in search1, search2 may be needed. When search2 is needed, the time taken will usually be much less than under the old mechanism because the search is much more limited and targeted using the new arrangement.
Paul White
But no, even if you're just playing around on a test system it wouldn't make sense to do that.
Paul White
You don't want to use undocumented flags.
Paul White
Right.
i-one replying to Paul White
TF8670 at query level
Paul White replying to i-one
How are you disabling search2?
i-one
@Paul, prior to SQL Server 2019 having `USE PLAN` and _search2_ disabled for a query causes optimizer to fail with _Msg 8698_. In SQL Server 2019 (with APF enabled) does it makes sense to disable _search2_ for a query, if I see that query compilation ends in _search2_, but taking its plan and supplying it to `USE PLAN` causes optimizer to end in _search1_? Or will it be redundant, because of optimizer will not step into _search2_ in this case anyway, or undesirable may be, because of it may want to step into _search2_ for some reason?
Josh Darnell replying to Paul White
Nice!  Great details and clarifications there.
Paul White
fixed
Paul White replying to Erik Darling
effectiveness
Erik Darling
effects? effectiveness?
Erik Darling
> You can monitor the effectives of the new mechanism
Paul White replying to Josh Darnell
Made a few edits.
Josh Darnell
Well it's off to a great start then haha.
Paul White replying to Josh Darnell
I ran out of time tonight. I might improve on it tomorrow.
Josh Darnell
@Paul Thanks for the answer :D  I'm still digesting it, but wanted to acknowledge I saw it.