Post reply

Exploring Parameter Sensitive Plan Optimization in SQL Server 2022

  • July 21, 2023 at 12:00 am

    #4223826

    Comments posted to this topic are about the item Exploring Parameter Sensitive Plan Optimization in SQL Server 2022

  • August 8, 2023 at 9:23 pm

    #4256206

    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!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply