I get an error running this query from the article:
ALTER DATABASE [database_name]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
Msg 102, Level 15, State 6, Line 2
Incorrect syntax near 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'.
Completion time: 2023-08-08T14:14:05.6642821-07:00
According to this article, the correct syntax is:
ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF
I wish to turn it off globally, and only turn it on for certain SPs. From your article, apparently this is possible at the SP level.
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.
I'm a little unclear on how to accomplish this though. Does that mean at the end of an EXEC statement for the SP, or at the end of the SP code itself?
Thank you for a great article!