A SaaS company wants Azure to recommend and create missing indexes automatically on its production database, but it does not want the platform to remove any existing index without review. Which automatic tuning configuration meets this requirement?
Show answer and explanation
Answer: D
Automatic tuning in Azure SQL Database offers separate options for creating indexes, dropping indexes and forcing the last good plan. Enabling CREATE_INDEX lets the service create indexes its analysis recommends, while leaving DROP_INDEX off ensures no existing index is removed automatically. This matches the company's requirement to add beneficial indexes without unreviewed removals. AUTO_SHRINK and clearing the plan cache are unrelated to index tuning, and enabling both index options would permit automatic removal.
Why the other options are wrong
- A. Enabling DROP_INDEX allows the service to remove indexes it judges unused. That breaks the company's requirement that no existing index be removed without review.
- B. AUTO_SHRINK shrinks database files and is generally discouraged because it causes fragmentation. It has no role in automatic index creation.
- C. Clearing the plan cache forces recompilation of queries and can cause temporary slowdowns. It does not create or manage indexes, so it cannot meet the stated requirement.