Performance and execution plan improvements in SQL Server 2025 (part 2)

SQL Server 2025 was officially released in November 2025. Join execution plan expert Hugo Kornelis as he takes an in-depth look at some of the new features that affect query performance and execution plans - such as Optional Parameter Plan Optimization, Optimized Locking, and Optimized Halloween Protection.
What problems are these features trying to solve? How do they work? And do they achieve what they set out to do ... or do they merely cause a whole new set of problems to look out for?

Explain Optional Parameter Plan Optimization. Explain Optimized Locking. Explain Optimized Halloween Protection. Show implementation, benefits, and drawbacks of each of these features.