Is it possible to add a query hint to an entire stored procedure in SQL Server 2022?

Question

SQL Server 2022 has a new feature called Parameter Sensitive Plan Optimization. I don't want to turn it on for my entire database, but I have four stored procedures that I believe would benefit from this feature. According to this article on SQL Server Central, it is possible to turn it on for specific queries or stored procedures:

To enable the PSP optimization at query level, Add the query hint OPTION (USE HINT('ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION')) just before the semicolon at the end of the query or stored procedure. Run the stored procedure or query. This stored procedure or query now has the feature enabled.

However, it is unclear how to turn it on for a SP. Do they mean the semicolon on the EXEC line (runtime), or in the SP code itself? I've done Google searches but found nothing showing an example, which leads me to believe that it is not actually possible. If not, I will have to add it to the end of each query in the SPs - not an impossible task (I've done it before), but the article left me with some hope that it could be accomplished more easily. Like maybe a new feature of 2022.

Answer 1

There is no option in the grammar to apply this to the stored procedure itself.

If the hint existed you would need to apply it to all the relevant SQL Statements inside the four stored procedures.

I don't see any sign this hint exists though

select *
from sys.dm_exec_valid_use_hints
where name = 'ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION'

Returns nothing and it is not listed as a valid hint.

Neither of the above are conclusive however because Paul White points out that the same is true for DISABLE_PARAMETER_SENSITIVE_PLAN - which does exist as an apparently undocumented hint.

Crucially though trying ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION or ENABLE_PARAMETER_SENSITIVE_PLAN against the latest CU (CU7) returns error message

'ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION' is not a valid hint.

And searching Google for "ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION" returns only this question and the SQL Server Central article so unclear where this hint was sourced from!