-2

Where can I check if the PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is enabled or not on a db?

I tried looking for it in sys.databases and also under the database properties UI.

Is query store enabling a prerequisite for this feature?

0

2 Answers 2

2

To see how it is currently configured you can check:

    USE [DATABASENAME]
    SELECT * 
    FROM sys.database_scoped_configurations
    WHERE [name]='PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'

To turn it on use:

    USE [DATABASENAME]
    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON

And to turn it off use:

    USE [DATABASENAME]
    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF
-2

PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is enabled by default in SQL Server 2022. However, it may be turned off at the query or database level. To disable it at the database level:

ALTER DATABASE [database_name] SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

To turn it back on, use:

ALTER DATABASE [database_name] SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

1
  • I have migrated few databases, so for them I want to check if it is enabled or not. Is there a simple query to check this?
    – variable
    Commented Oct 14, 2023 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.