Exam DP-300 topic 4 question 2 discussion

SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver15

The right answer is as Tomi1234 say because is a VM SQL Server: Box1 - SET QUERY_STORE = ON (OPERATION MODE = READ_WRITE); Box2 - SET AUTOMATIC_TUNNING (FORCE_LAST_GOOD_PLAN = ON); https://www.sqlshack.com/understanding-automatic-tuning-in-sql-server-2017/ If the server may be Azure SQL or Managed Instance then the response should be: Box1. SET AUTOMATIC_TUNNIG=AUTO Box2. SET AUTOMATIC_TUNNING (FORCE_LAST_GOOD_PLAN = ON) and other options are SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = OFF) https://docs.microsoft.com/es-es/azure/azure-sql/database/automatic-tuning-enable

The option SET AUTOMATICS TUNING = AUTO does not exist so it seems. For <automatic_tuning_option> ::= { AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = { DEFAULT | ON | OFF } ) } https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver16

In case of error messages that automated recommendation management has been disabled, or simply disabled by system, the most common causes are: Query Store is not enabled, or Query Store is in read-only mode for a specified database, or Query Store stopped running because it used the allocated storage space.

Answers in green (in example) are correct. https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable

Correct Answer: SET QUERY_STORE = ON (OPERATION MODE = READ_WRITE); SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); Reason: As this is SQL Server hosted on Azure VM and not Azure SQL DB / MI, Query store needs to be first enabled and then automatic plan needs to be applied.

And the proof. Find a database w/o Query Store enabled and run the following query. You will get an error telling you Query Store must be enabled to turn on automatic tuning. ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

In reviewing this another rationale occurs to me. From the documentation Force_Last_Good_Plan_On should be correct (https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver15) The aha moment is that the cache only keeps one execution plan at a time. (Technically 2. 1 for serial execution and 1 for any parallel execution). To be able to force last good plan SQL Server needs a way to determine what the last good plan was. The only place it will get that information from is the Query Store so that HAS to be enabled first.

If this question was about an Azure SQL database, I'd agree. The first part is redundant, but doesn't cause any issue. The problem is that the question states that this is SQL Server on a VM. In other words, not an Azure SQL DB. In that case, for automatic tuning to work Query Store must first be enabled. https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver15 Because of that I actually think the answer should be to turn on query store and then turn on automatic tuning.

The reference is wrong as it applies only to SQL Database and SQL managed instance. Tomi1234 provided the right answer

As a pre-requisite, Query Store must be turned on. Then, to enable automatic tuning, you will use this command: ALTER DATABASE <DBNAME> SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); https://blog.pythian.com/sql-server-2017-automatic-tuning/

enable query store to read write then auto tuning = AUTO, check https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable#disabled-by-the-system

You're right. What I mentioned regards to on-prem env.

The answer is correct. If you follow the link given, there is clear explanation supporting the selected answers.