What should you configure?

You have an Azure SQL database.

You discover that the plan cache is full of compiled plans that were used only once.

You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.

You need relieve the memory pressure.

What should you configure?
A . LEGACY_CARDINALITY_ESTIMATION
B . QUERY_OPTIMIZER_HOTFIXES
C . OPTIMIZE_FOR_AD_HOC_WORKLOADS
D . ACCELERATED_PLAN_FORCING

View Answer

Answer: C

Explanation:

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first

time. The default is OFF. Once the database scoped configuration

OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored

in cache when a batch is compiled for the first time. Plan stubs have a smaller memory footprint

compared to the size of the full compiled plan.

Incorrect Answers:

A: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database.

B: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM).

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-

transact-sql