Automatic Tuning interviews tend to split candidates into two camps: people who describe it as “the thing that fixes indexes for you,” and people who can actually explain what it’s watching, what it’s deciding, and — just as importantly — when they’d choose not to trust it fully. This list is built to get you into the second camp.
Split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), written the way you’d actually talk it through in a room — the reasoning behind each answer, not a settings-page description.
One framing worth carrying through the whole list: Automatic Tuning is genuinely good automation, but good automation still needs a human who understands what it’s automating. The strongest answers below aren’t “it just works” — they’re “here’s what it does well, here’s where I’d still want a human checking, and here’s why.”
Beginner Level
1. What is Automatic Tuning in Azure SQL? A built-in feature that continuously monitors query performance using Query Store data and can automatically apply performance improvements — creating missing indexes, dropping unused ones, and reverting to a previously better-performing execution plan when a regression is detected — without a DBA manually running the analysis or applying the fix themselves.
2. What are the three Automatic Tuning options in Azure SQL Database? CREATE INDEX (automatically creates indexes the engine identifies as beneficial), DROP INDEX (automatically removes indexes that aren’t being used and are just adding write overhead), and FORCE LAST GOOD PLAN (automatically reverts to a previously well-performing execution plan when it detects a regression).
3. Does Automatic Tuning require any extra setup to start working? Query Store needs to be active, since Automatic Tuning relies entirely on the data it captures — but Query Store is enabled by default on Azure SQL Database, so in most cases the underlying data collection is already happening. You still choose, per option, whether to let the platform apply changes automatically or just recommend them.
4. What’s the difference between “automatic” mode and “recommend only” mode for each tuning option? In automatic mode, the platform applies the change itself without asking — you find out after the fact, generally through a notification or the tuning history. In recommend-only mode, the system tells you what it would do and why, but a human has to explicitly approve and apply it — giving you the same insight with a manual approval gate in between.
5. What is Query Store, and why does Automatic Tuning depend on it? Query Store is a built-in feature that captures query text, execution plans, and runtime performance statistics directly inside the database over time. Automatic Tuning depends on it because every decision it makes — a missing index recommendation, a detected plan regression — is derived from patterns in that captured history, not from a live, one-time analysis.
6. What does “FORCE LAST GOOD PLAN” actually mean in plain terms? When a query that used to run fast suddenly starts running much slower — usually because the optimizer picked a new, worse execution plan — this feature notices the regression and tells the engine to go back to using the previous, known-good plan instead, without anyone having to manually diagnose and fix it.
7. Can Automatic Tuning make a database slower? It’s specifically designed to avoid that — before permanently keeping a change, particularly for CREATE INDEX and FORCE LAST GOOD PLAN, the system continues monitoring afterward and can revert its own decision if the change doesn’t actually deliver the expected improvement. It’s not a one-shot “apply and forget” action; it keeps watching.
8. Where do you configure Automatic Tuning settings in the Azure Portal? Under the database’s (or server’s) “Automatic tuning” blade, where you can toggle each of the three options independently between automatic apply, recommend only, or off, either at the individual database level or inherited from the server-level default.
9. Can Automatic Tuning settings be inherited from the server level? Yes — you can set a default policy at the logical server level that individual databases inherit unless they’re explicitly overridden, which is useful for keeping a consistent tuning policy across many databases without configuring each one separately.
10. What kind of problems does CREATE INDEX automatic tuning actually solve? It solves the specific, common scenario where a query is doing a full table scan (or an inefficient partial index usage) because a genuinely useful index simply doesn’t exist yet — the same class of problem a DBA would traditionally find by reviewing missing index DMV recommendations manually, just applied automatically based on real observed usage patterns rather than a one-time check.
11. Is Automatic Tuning only available on Azure SQL Database, or does it work on Managed Instance too? It’s available on both Azure SQL Database and Azure SQL Managed Instance, though the specifics of what’s configurable and how it surfaces in tooling differ slightly between the two.
12. What’s the difference between Automatic Tuning and Azure Advisor’s performance recommendations? They’re closely related and often surface similar underlying findings, but Advisor is a broader recommendation engine you review manually across many resource types, while Automatic Tuning is specifically about database performance and can actually apply the fix itself if you let it, not just recommend it.
13. Does enabling Automatic Tuning mean you no longer need to manually review query performance? No — it handles a meaningful chunk of the routine, well-understood tuning work, but it doesn’t replace understanding your workload, reviewing genuinely complex performance problems, or making judgment calls about trade-offs (like write overhead from a new index) that the automated system isn’t positioned to weigh on its own.
14. What happens if you turn Automatic Tuning off after it’s already applied some changes? Changes it already applied (indexes it created, plans it forced) generally remain in place — turning the feature off stops it from making new changes going forward, it doesn’t automatically undo what it already did. You’d review and manually revert anything you specifically want undone.
15. Can you see a history of what Automatic Tuning has done to a database? Yes — the tuning recommendations and applied-actions history is visible in the portal’s Automatic Tuning blade and also queryable directly via T-SQL (sys.dm_db_tuning_recommendations), showing what was recommended or applied, when, and the reasoning/estimated impact behind it.
16. What’s a simple reason a fresher DBA might want to start with “recommend only” mode rather than full automatic mode? It lets you build trust and understanding by seeing exactly what the system would do and why, before letting it act on its own — a good way to learn what patterns trigger recommendations while still keeping a human decision point in the loop until you’re confident in how it behaves for your specific workload.
17. Is Automatic Tuning something you’d typically enable on a brand-new, low-traffic development database? It’s not particularly harmful there, but it’s also not very useful yet — Automatic Tuning’s recommendations are only as good as the query pattern history it’s observed, and a low-traffic dev database simply hasn’t generated enough real usage data for its recommendations to be meaningful yet.
Intermediate Level
18. Walk through exactly how FORCE LAST GOOD PLAN detects a regression. Query Store continuously tracks multiple execution plans per query along with their runtime statistics over time. When a query’s recent average performance (CPU, duration) degrades significantly compared to its own historical baseline — and specifically when a different, previously-used plan for that same query performed notably better — the feature identifies this as a regression and forces the historically better plan back into use, rather than waiting for a human to notice the slowdown and manually investigate.
19. What’s the difference between a plan regression Automatic Tuning would catch versus a genuine performance problem it wouldn’t help with? A plan regression is when the same logical query had a better plan before and a worse one now — purely a plan-choice problem, which forcing the last good plan directly fixes. A genuine performance problem — a missing index that’s never existed, a fundamentally inefficient query design, insufficient resources for the workload — isn’t a regression from a previous state at all, so there’s no “last good plan” to revert to, and Automatic Tuning’s CREATE INDEX option (or manual tuning) is the relevant tool instead, not FORCE LAST GOOD PLAN.
20. How does Automatic Tuning validate that an automatically created index is actually beneficial, rather than just applying every missing index suggestion blindly? After creating an index, it continues monitoring the affected queries’ performance and resource consumption, and if the index doesn’t produce a meaningful, sustained improvement (or if it’s determined to not be worth the added write overhead), the system can automatically revert by dropping the index it created — it’s a continuous validate-and-adjust loop, not a fire-and-forget action based purely on the initial missing-index estimate.
21. What’s the interaction between CREATE INDEX and DROP INDEX automatic tuning options over time on an evolving workload? Together they form a feedback loop that adapts to a workload’s actual usage patterns — CREATE INDEX adds indexes that emerging query patterns genuinely benefit from, while DROP INDEX removes indexes that usage has drifted away from and are no longer earning their write-overhead cost, keeping the index footprint aligned with how the database is actually being queried right now, not how it was queried when someone last manually reviewed indexing months or years ago.
22. How would you audit what Automatic Tuning has done on a database using T-SQL, without relying on the portal? Query sys.dm_db_tuning_recommendations for current recommendations and their state (pending, applied, reverted), and Query Store’s own views (sys.query_store_query, sys.query_store_plan) to inspect the underlying plan history that informed a specific FORCE LAST GOOD PLAN decision — useful for a DBA who wants to verify or explain a specific automated action without depending on the Azure Portal being available or convenient at that moment.
23. What’s a scenario where you’d deliberately disable DROP INDEX automatic tuning even if CREATE INDEX and FORCE LAST GOOD PLAN remain enabled? A database with genuinely seasonal or infrequent-but-critical query patterns — an index that supports a quarterly reporting job might show as “unused” for months at a time and be a drop candidate by the system’s usage-based logic, even though it’s essential when that reporting job actually runs. In that case I’d keep DROP INDEX off (or in recommend-only mode) specifically to avoid the system removing something that’s genuinely needed but just infrequently exercised.
24. How does Automatic Tuning behave differently across the DTU and vCore purchasing models, if at all? The tuning logic and decision-making itself isn’t fundamentally different between the two models — it’s based on Query Store data regardless of purchasing model. What differs is the resource context those decisions are made within; a CREATE INDEX decision’s cost/benefit calculation is still relative to the database’s actual available compute and I/O capacity, which is expressed differently (blended DTU vs separated CPU/memory) depending on which model you’re on, but the underlying tuning mechanism itself is the same.
25. What’s the relationship between Automatic Tuning and Intelligent Query Processing (IQP) features like Adaptive Joins or Memory Grant Feedback? They’re complementary but distinct — Automatic Tuning makes discrete, visible decisions (create this index, force this plan) based on Query Store history, while IQP features like Adaptive Joins and Memory Grant Feedback are continuous, plan-execution-time adjustments the engine makes on its own, without a separate “recommendation” being generated or a settable on/off policy the same way Automatic Tuning has. Both reduce the manual tuning burden, but through different mechanisms operating at different points in a query’s lifecycle.
26. How would you explain the cost/benefit trade-off Automatic Tuning is implicitly making when it decides to create an index? It’s weighing the estimated read benefit (how much faster the affected queries would run, and how frequently they execute) against the estimated write overhead the new index adds to every insert/update/delete touching those columns — the system has visibility into both sides of that trade-off through Query Store’s captured execution statistics, which is exactly the kind of trade-off a DBA would traditionally have had to reason through manually using DMV data.
27. Can Automatic Tuning recommendations conflict with manually created indexes or manually forced plans, and how does that get resolved? If a DBA has manually forced a specific plan for a query, Automatic Tuning generally respects that manual override rather than fighting it — the system is designed to layer on top of manual decisions, not silently override them. For indexes, it’s possible (though the system tries to avoid clearly redundant recommendations) to end up with some overlap between manually created and automatically created indexes, which is a good reason to periodically review the full index list rather than assuming automation alone keeps it perfectly clean.
28. What’s the practical difference between disabling Automatic Tuning entirely versus setting all three options to “recommend only”? Disabling it entirely stops the system from generating recommendations at all — you lose the ongoing analysis, not just the automatic application. Recommend-only mode keeps the analysis and recommendation generation running continuously, just without auto-applying — meaning you still get the visibility and the underlying Query Store-driven insight, with a manual approval step, which is generally the more useful middle ground for a team wanting oversight without losing the analytical value entirely.
29. How would you use Automatic Tuning’s recommendation history as a learning tool for a junior DBA? I’d have them review recommend-only mode findings alongside the actual Query Store data that justified each one — walking through why a specific missing index recommendation makes sense given the query’s filter and join pattern, or why a specific plan was flagged as regressed — turning the automated system’s reasoning into a teaching example of the same analysis a DBA would do manually, rather than just treating the recommendation as a black-box answer to blindly accept.
30. What’s a limitation of FORCE LAST GOOD PLAN when the “last good plan” itself is no longer actually appropriate due to data growth or distribution changes? If the underlying data has changed meaningfully since that “good” plan was originally good — the table’s grown substantially, or the data distribution the plan was optimized for has shifted — forcing that old plan back might not actually be the best choice anymore, even though it’s better than the immediately preceding regression. This is a case where I’d want to review the forced plan periodically rather than assuming a forced plan remains correct indefinitely, since the system’s comparison is relative (better than the regression), not an absolute guarantee it’s the best possible plan for current data.
31. How do you determine whether Automatic Tuning’s CREATE INDEX recommendations are actually improving overall database health, versus just optimizing individual queries in isolation? I’d look at the aggregate picture, not just individual query improvement — overall database resource consumption trends (CPU, I/O) before and after a period of automatic index creation, along with total index count and their combined write overhead — because a series of individually-reasonable single-query optimizations can still add up to excessive index bloat on a heavily-written table if reviewed only one recommendation at a time rather than holistically.
32. What’s the significance of Automatic Tuning being tied to a specific database rather than a server-wide setting that applies uniformly regardless of workload? Different databases on the same server can have very different workload characteristics — a read-heavy reporting database benefits from more aggressive indexing, while a write-heavy OLTP database might need more caution around CREATE INDEX given the write overhead trade-off — so per-database configuration (even when inheriting a server default) lets you tailor the policy to what actually makes sense for each workload rather than a one-size-fits-all setting.
33. How would you handle a situation where Automatic Tuning created an index that’s technically helping the query it targeted, but is measurably hurting overall write throughput on a high-volume table? I’d manually review and likely remove or replace that index with a more efficient, narrower one — a covering index that satisfies the read need with less overhead, if possible — since the automated system’s per-recommendation evaluation might not fully weigh cross-cutting impact on a table under heavy concurrent write load the way a DBA reviewing the whole table’s index and workload picture would. This is a clear case where recommend-only mode, or at least active periodic review, earns its keep over blind full automation.
34. What role does Automatic Tuning play specifically during and after a migration from on-prem SQL Server? Right after migration, Query Store starts building a fresh history against the new environment’s actual performance characteristics, so Automatic Tuning’s recommendations in the early days post-migration are based on limited data and worth treating cautiously — I’d typically run in recommend-only mode for the first few weeks post-migration specifically to build confidence in what it’s suggesting before trusting it with full automatic apply, rather than assuming it’s immediately as reliable as it would be against a database with months of stable, representative history behind it.
35. Can Automatic Tuning help identify a parameter sniffing issue, and if so, how? Indirectly, yes — a query suffering from severe parameter sniffing (a plan that’s great for one parameter value and terrible for another) can present as exactly the kind of “this query’s performance suddenly got much worse” pattern FORCE LAST GOOD PLAN is designed to catch, and forcing a better historical plan can genuinely mask the symptom. But it doesn’t diagnose parameter sniffing as the root cause the way a DBA manually comparing execution plans across different parameter values would — it’s a symptom-level mitigation, not a root-cause fix, which is worth understanding rather than assuming the automated fix means the underlying issue is actually resolved.
Advanced Level
36. Design a governance policy for Automatic Tuning across a large, mixed-criticality database fleet, balancing automation benefit against control requirements. I’d tier the policy by database criticality and change-control requirements rather than applying one blanket setting — low-risk, non-regulated databases get full automatic mode across all three options to maximize hands-off benefit, while regulated or genuinely business-critical databases run in recommend-only mode with recommendations routed into the normal change-approval workflow, so schema changes still get reviewed even though the analysis remains fully automated. I’d also build a periodic audit process reviewing the tuning history across the fleet — not to second-guess every decision, but to catch systemic patterns (like a specific tuning option consistently getting reverted, which might suggest it’s not well-suited to a particular workload type) that individual per-database review wouldn’t surface.
37. Explain the internal mechanism by which Automatic Tuning decides an automatically created index isn’t delivering value and should be reverted, and what data it’s actually evaluating. It continues capturing the affected queries’ Query Store runtime statistics after the index is created, comparing actual observed performance (duration, CPU, logical reads) against the baseline that existed before the index and against the improvement that was originally estimated when the recommendation was generated — if the realized improvement falls meaningfully short of what was estimated, or if overall database performance metrics suggest the index’s write overhead is a net negative, the system treats that as grounds to revert. This is fundamentally an empirical, observed-outcome-based validation loop, not a one-time estimate the system just trusts and walks away from.
38. How would you reconcile Automatic Tuning’s index management with a broader Database DevOps practice using schema-as-code and version-controlled migrations? This is a genuine tension worth addressing explicitly rather than ignoring — an automatically created index that isn’t reflected in your source-controlled schema definition creates drift between what’s actually deployed and what your migration scripts describe, which can cause confusion or even conflicts during a future deployment. I’d generally run CREATE INDEX and DROP INDEX in recommend-only mode for any database managed under strict schema-as-code practices, treating accepted recommendations as a trigger to add a proper, reviewed migration script rather than letting the automated system’s direct changes silently diverge from the version-controlled source of truth.
39. What’s the risk of Automatic Tuning’s FORCE LAST GOOD PLAN masking a genuine, worsening data-growth-driven performance problem rather than a one-time regression? If query performance is gradually degrading because the table is genuinely growing and no plan — old or new — actually scales well at the new size, FORCE LAST GOOD PLAN might keep reverting to whatever the “least bad” historical plan was, giving a false sense that the situation is being handled, when the real underlying need is a schema or indexing change to actually address the growth, not a plan choice fix. I’d specifically watch for a query that keeps getting flagged and force-reverted repeatedly over time as a signal that the automated mitigation isn’t actually solving the root cause, and treat repeated forcing on the same query as an escalation trigger for manual investigation, not a sign the automation is working well.
40. How would you design monitoring and alerting specifically around Automatic Tuning’s own behavior, separate from general database performance monitoring? I’d alert on Automatic Tuning actions themselves — a new index created, an index dropped, a plan forced — routed to the team via the Activity Log or a scheduled query against sys.dm_db_tuning_recommendations, specifically so changes to the database’s actual physical structure and query plans are visible to the team as they happen, not just discoverable if someone happens to check the portal. I’d also alert specifically on repeated reverts of the same recommendation, since that pattern (as in the previous question) suggests something the automation is struggling with, worth a human look.
41. Explain a scenario where enabling all three Automatic Tuning options in full automatic mode on a Business Critical, highly regulated production database would be a mistake, even though the feature is technically fully capable of handling it safely. Even if the feature works exactly as designed, a regulated environment often has change-control and audit requirements that aren’t really about whether the change was correct — they’re about whether it was reviewed and approved through a defined process before being applied to production. An unattended index creation or removal, however beneficial, that bypasses that review process is itself a compliance and audit finding waiting to happen, independent of whether the technical outcome was good — which is why “the automation is reliable” and “the automation is appropriate for full auto-apply in this specific environment” are genuinely different questions with potentially different answers.
42. How would you approach validating Automatic Tuning’s effectiveness quantitatively across a fleet, for a report to leadership justifying continued or expanded use? I’d pull the tuning action history across the fleet, cross-referenced against resource consumption trends (CPU, I/O) for the affected databases before and after tuning actions, and specifically report both sides honestly — the queries that measurably improved, and any reverted actions that didn’t pan out — rather than only presenting a favorable summary, since an honest accounting including the occasional revert is actually more credible and useful for a leadership decision about expanding automation than a report implying it’s flawless.
43. What’s the interaction between Automatic Tuning and read scale-out — does a tuning decision made based on primary replica query patterns also apply correctly to read-only secondary replicas? Automatic Tuning’s decisions (index creation, plan forcing) apply to the database itself, and since Business Critical’s HA replicas share the same underlying data and schema, an index created via Automatic Tuning is present on secondaries too — but the tuning analysis itself is generally based on Query Store data captured on the primary. I’d specifically consider whether read-only reporting traffic routed to a secondary has a genuinely different query pattern than the primary’s OLTP traffic, in which case index needs identified from primary-side analysis might not fully address what the secondary’s actual read-scale-out workload needs — worth validating rather than assuming primary-driven tuning automatically optimizes the secondary’s distinct usage pattern too.
44. How would you handle Automatic Tuning configuration and behavior differences when part of a fleet has recently migrated from on-prem SQL Server and part is long-running, cloud-native? I’d apply a more conservative, recommend-only default to recently migrated databases specifically because their Query Store history is still shallow and not yet representative of stable, real-world usage patterns, while allowing more aggressive automatic settings on databases with months or years of stable history behind them — treating “how long has this database been generating trustworthy Query Store data” as an explicit factor in the tuning policy, not applying a uniform fleet-wide setting regardless of each database’s actual history depth.
45. Explain how you’d design a rollback procedure specifically for an Automatic Tuning action that turns out, after the fact, to have caused a problem the system itself didn’t catch and revert on its own. I’d query the tuning recommendation history to identify exactly what was applied and when, correlate that timestamp against when the problem actually started (via monitoring or user reports) to confirm causation rather than assuming it, then manually revert the specific action — dropping an automatically created index, or un-forcing a plan via sp_query_store_unforce_plan — while also considering whether to temporarily disable that specific tuning option on that database while investigating why the system’s own self-validation didn’t catch the issue on its own.
46. How would Automatic Tuning behave differently, if at all, in a Hyperscale database compared to General Purpose, given Hyperscale’s different storage architecture? The fundamental Query Store-driven decision logic isn’t different, but the underlying cost of index creation and the I/O characteristics being optimized against differ given Hyperscale’s distributed page-server storage model — I’d specifically validate that recommendations and their estimated benefit are being evaluated against Hyperscale’s actual performance characteristics at realistic scale, since a feature well-validated primarily against General Purpose-scale databases might have different practical dynamics against a very large Hyperscale database, worth confirming empirically rather than assuming identical behavior across service tiers.
47. What’s your approach to explaining Automatic Tuning’s limitations to a team that’s begun treating it as a full substitute for having a DBA review performance regularly? I’d walk through concretely what it can’t do — recognize a fundamentally bad schema design, weigh genuine business-priority trade-offs between competing queries’ needs, catch a data-model-level problem no index or plan choice can fix, or make the judgment call about whether an automated change is appropriate given a specific regulatory or change-control context — framing it not as “don’t trust the automation” but as “this automation handles the mechanical, well-understood part of tuning extremely well, freeing up DBA time for the harder judgment-call problems it was never designed to solve in the first place.”
48. How would you design an A/B or staged rollout approach for changing Automatic Tuning policy (say, from recommend-only to full automatic) across a large fleet, rather than a blanket switch? I’d start with a representative subset of lower-risk, well-understood databases, monitor their tuning action history and resulting performance/stability closely for a defined period, and only expand to the broader fleet once that pilot group demonstrates the automation is behaving as expected in your specific environment and workload types — rather than assuming Microsoft’s general validation of the feature automatically means it’s proven safe for full automatic mode across your entire, potentially quite different, fleet on day one.
49. Explain a case where Automatic Tuning’s CREATE INDEX and a manually designed indexing strategy would genuinely conflict, and how you’d resolve the conflict at the design level, not just case by case. If a DBA has a deliberate, holistic indexing strategy for a table — say, intentionally keeping a narrow set of indexes to minimize write overhead on a very high-throughput table, accepting that a few less-common queries run somewhat slower as a conscious trade-off — Automatic Tuning’s CREATE INDEX could keep proposing (or applying, in full automatic mode) an index that technically helps one of those deprioritized queries but works against the deliberate overall strategy. I’d resolve this at the design level by explicitly setting CREATE INDEX to recommend-only (or off) specifically on tables under an intentional, already-reasoned-through indexing strategy, rather than treating every table in the database as a candidate for the same default automated policy.
50. Walk through a genuinely difficult Automatic Tuning-related situation you’ve handled or would expect to handle, end to end. Intentionally open-ended, because interviewers are evaluating judgment, not a memorized script. A strong structure to demonstrate regardless of the specific scenario: (1) understand what the automated system actually did and why, using the real tuning history and Query Store data behind the decision, rather than treating it as an unexplainable black box, (2) determine whether the outcome is genuinely a problem with the automation’s judgment or a legitimate trade-off that just needs broader context the system didn’t have, (3) take the narrowest corrective action that addresses the actual issue — reverting a specific action, adjusting policy for a specific table, rather than disabling automation fleet-wide over one bad outcome, (4) feed what was learned back into policy — a more conservative default for a certain database category, an explicit exclusion for tables with deliberate manual strategies, and (5) keep the automation’s real value in view throughout — the goal is calibrating trust based on evidence, not abandoning genuinely useful automation because of one edge case, nor blindly trusting it because it’s usually right. Interviewers consistently favor candidates who describe calibrating trust in automation based on evidence over candidates who describe either extreme — full blind trust, or dismissing the feature’s real value over a single bad experience.
A Closing Thought
The thread running through nearly every advanced answer here: Automatic Tuning is a genuinely well-designed, self-validating feedback loop — but “self-validating” doesn’t mean “unsupervised forever.” The senior-level skill isn’t distrusting the automation; it’s knowing exactly which category a given database or situation falls into, and calibrating how much you let it run on its own accordingly.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
