Hotspot Question You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit. For each of the following statements, select Yes if…

Question

Exhibits

Answer Area

• Nonclustered indexes will be added to tables to improve performance.No
• Columns will be added to existing indexes automatically.No
• The query execution plan will revert to a previous plan if query performance degrades.Yes

Explanation

1. Nonclustered indexes will be added to tables to improve performance. => No The exhibit shows that automatic tuning inherits from Azure defaults. The official explanation for the 'CREATE INDEX' option states, "By default CREATE INDEX is disabled. It is here configured as INHERIT so it is disabled." This confirms that the automatic creation of nonclustered indexes is disabled.
2. Columns will be added to existing indexes automatically. => No The 'CREATE INDEX' automatic tuning option in Azure SQL Database primarily focuses on creating new nonclustered indexes; it does not automatically modify existing indexes by adding columns. Furthermore, as established for the first statement, the CREATE INDEX feature is effectively disabled, so no new indexes will be created or existing ones modified in this manner.
3. The query execution plan will revert to a previous plan if query performance degrades. => Yes The exhibit shows the 'FORCE PLAN' option as 'ON Auto-configured by Azure'. The official explanation clarifies this as 'FORCE LAST GOOD PLAN' (automatic plan correction), which "Identifies Azure SQL queries using an execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan." This directly confirms the statement.

No community discussion yet for this question.