Query Store is one of those features everyone name-drops in an interview, but relatively few candidates can actually explain at the level of “here’s what it’s storing, here’s why that specific design choice matters, here’s a real scenario where I used it to find a root cause.” That gap is exactly what this list is built to close.
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 feature description lifted from documentation.
One framing worth carrying through the whole list: Query Store’s entire value proposition comes down to one idea — it turns “I think this query used to be faster” from a guess into a provable, timestamped fact. Almost every good answer below traces back to that core idea in some form.
Beginner Level
1. What is Query Store, and what problem does it solve? A built-in feature that automatically captures query text, execution plans, and runtime performance statistics directly inside the database over time. It solves the classic “this used to be fast, now it’s slow, but I have no historical data to prove it or see what changed” problem — instead of guessing, you can look at an actual timeline of a query’s performance and plan history.
2. Is Query Store enabled by default in Azure SQL Database? Yes — it’s on by default for every new Azure SQL Database, unlike on-prem SQL Server where it has to be manually enabled. This matters because it means historical data is already accumulating from day one, without anyone needing to remember to turn it on.
3. What are the three main types of data Query Store captures? Query text and plan information (what the query looks like and how the optimizer chose to execute it), runtime statistics (duration, CPU, logical reads, and more, aggregated over configurable time intervals), and wait statistics (what the query spent time waiting on, when correlated).
4. How is Query Store different from the plan cache? The plan cache holds only the currently active compiled plans, gets cleared on various events (a restart, memory pressure, certain configuration changes), and has no historical dimension — it’s a snapshot of right now. Query Store persists data to disk, survives restarts, and specifically tracks how a query’s plans and performance have changed over time, not just what’s happening right now.
5. Where is Query Store data actually stored? Inside the user database itself, in dedicated internal tables — not in tempdb, and not in a separate system database — meaning the data persists with the database, including through backup/restore and geo-replication, since it’s part of the database’s own physical storage.
6. What is Query Performance Insight, and how does it relate to Query Store? Query Performance Insight is a portal-based visual layer built entirely on top of Query Store’s captured data — it doesn’t collect anything independently. It’s essentially a friendlier, no-KQL-required way to view the same underlying Query Store information, useful for someone who wants a quick visual answer without querying the Query Store views directly.
7. What is a “regressed query” in Query Store terminology? A query whose recent performance has measurably gotten worse compared to its own historical baseline — Query Store has a dedicated built-in view specifically for surfacing these, since finding regressions is one of its most common and valuable use cases.
8. What does it mean for Query Store to track “multiple plans” for the same query? A single query (identified by its query hash) can have more than one execution plan associated with it over time — the optimizer might choose different plans due to parameter sniffing, statistics changes, or index changes — and Query Store keeps all of them, along with the performance data for each, rather than only remembering the most recent one.
9. What is FORCE LAST GOOD PLAN, and how does it use Query Store data? An Automatic Tuning feature that uses Query Store’s tracked plan history to detect when a query has regressed compared to a previously better-performing plan, and forces the engine to use that historically better plan going forward — a direct, practical application of Query Store’s multi-plan tracking.
10. Can you manually force a specific plan using Query Store, without Automatic Tuning? Yes — sp_query_store_force_plan lets a DBA manually pick a specific historical plan (identified by its plan ID) and force the engine to use it for future executions of that query, giving direct manual control independent of the automated FORCE LAST GOOD PLAN feature.
11. What’s the difference between an “estimated” and an “actual” execution plan, and does Query Store capture both? Estimated plans reflect what the optimizer predicted before running; actual plans reflect what genuinely happened, including real row counts. Query Store primarily captures the compiled plan (closer to estimated) along with separately aggregated actual runtime statistics from real executions — giving you both the plan shape and how it truly performed in practice, correlated together.
12. How would you view Query Store data through the Azure Portal, without writing any queries? Through the database’s Query Performance Insight blade for a simplified visual view, or the more detailed Query Store blade (with built-in reports like Top Resource Consuming Queries, Regressed Queries, and Query Wait Statistics) for a more thorough, still-graphical exploration.
13. What is the “Top Resource Consuming Queries” report, and when would you use it? A built-in Query Store report showing which queries have consumed the most of a chosen resource (CPU, duration, execution count, and others) over a selected time window — a natural starting point when you want to know “what’s actually driving load on this database” without a specific query already in mind.
14. Does Query Store capture every single query execution, or does it sample/aggregate? It aggregates runtime statistics into configurable time intervals (by default, hourly) rather than storing a row for every single individual execution — meaning you get a statistically meaningful summary of performance over each interval, not a full execution-by-execution log, which keeps the storage footprint manageable.
15. What is Query Store’s retention/cleanup behavior — does data stay forever? No — Query Store has a configurable retention period (data older than this is automatically cleaned up) and a configurable maximum storage size, after which older data is purged to make room, or Query Store can switch into a read-only state if it hits its size cap without cleanup enabled — meaning historical depth is genuinely limited by these settings, not indefinite.
16. What’s a simple, beginner-friendly reason to check Query Store first when someone reports “the database feels slower than it used to”? It directly answers the “compared to when, and by how much” question with real historical data, rather than relying on a vague impression — you can look at a specific query’s performance trend line and immediately see whether it genuinely got slower, when that started, and whether it correlates with a plan change, turning a vague complaint into a concrete, investigable fact.
17. Is Query Store available on Azure SQL Managed Instance and SQL Server on Azure VM as well, or only Azure SQL Database? It’s available on all three — Azure SQL Database (on by default), Managed Instance (also on by default), and SQL Server on Azure VM (available since SQL Server 2016, but needs to be manually enabled there, just like on-prem, since it’s not automatically on by default outside the PaaS offerings).
Intermediate Level
18. Walk through the internal structure of Query Store — what are the main catalog views, and what does each hold? sys.query_store_query holds one row per distinct query, with metadata like when it was first seen and compilation-related info. sys.query_store_plan holds the plan(s) associated with each query, including the actual plan XML. sys.query_store_runtime_stats holds the aggregated performance statistics per plan per time interval. sys.query_store_wait_stats holds aggregated wait statistics correlated to specific queries and intervals. Joining these together is how you go from “here’s a query” to “here’s its plan and exactly how it’s performed over time.”
19. How would you write a query to find the top 10 most CPU-consuming queries over the last 24 hours using Query Store’s catalog views directly? Join sys.query_store_query to sys.query_store_plan to sys.query_store_runtime_stats, filter runtime_stats_interval_start_time to the last 24 hours, aggregate avg_cpu_time (or sum, depending on whether you want per-execution average or total consumption) grouped by query, and order descending — this is essentially rebuilding the portal’s “Top Resource Consuming Queries” report manually, useful when you need custom filtering the built-in report doesn’t offer.
20. What’s the difference between QUERY_CAPTURE_MODE options (ALL, AUTO, NONE, CUSTOM), and when would you choose something other than the default? ALL captures every query; AUTO (the default) captures queries based on resource consumption and execution count thresholds, filtering out trivial, cheap, one-off queries to reduce overhead; NONE stops capturing new queries entirely (existing data remains); CUSTOM lets you define your own capture policy thresholds. I’d consider ALL temporarily during a focused investigation where even infrequent, low-cost queries matter, but AUTO is generally the right steady-state choice to avoid Query Store filling up with noise.
21. How does Query Store’s MAX_STORAGE_SIZE_MB and cleanup policy interact, and what happens if you don’t configure cleanup properly? MAX_STORAGE_SIZE_MB sets a hard cap on Query Store’s internal storage footprint; if that cap is reached without an effective size-based cleanup policy configured, Query Store can switch to read-only mode — meaning it stops capturing new data entirely while still letting you query existing historical data. This is a common, easily-missed cause of “why does Query Store seem to be missing recent data” — checking actual_state in sys.database_query_store_options reveals whether this has happened.
22. What’s the difference between Query Store’s time-based retention (STALE_QUERY_THRESHOLD_DAYS) and its size-based cleanup, and why do you need to think about both? Time-based retention purges data older than a configured number of days regardless of overall size. Size-based cleanup kicks in when the storage cap is approached, potentially purging data faster than the time-based setting alone would suggest if the database generates a high volume of distinct queries. A database with a generous time-based retention setting can still lose data earlier than expected if size-based cleanup is triggering first — the two settings need to be considered together, not just one in isolation.
23. How would you use Query Store to specifically investigate a suspected parameter sniffing issue? Look at a specific query’s runtime statistics broken out by plan (since parameter sniffing often shows up as the same query having multiple distinct plans with meaningfully different performance profiles), and check whether performance variance correlates with specific parameter value ranges — Query Store won’t show you the literal parameter values by default, but the presence of multiple plans with wildly different average duration for the same query text is a strong parameter-sniffing signal worth investigating further, often alongside Extended Events if you need the literal parameter values themselves.
24. What’s the practical difference between forcing a plan via Automatic Tuning’s FORCE LAST GOOD PLAN versus manually via sp_query_store_force_plan? Automatic Tuning continuously monitors and can both apply and revert a forced plan on its own if it later determines the forced plan isn’t actually the best choice anymore. A manual force via sp_query_store_force_plan stays forced until a human explicitly un-forces it (sp_query_store_unforce_plan) — meaning manual forcing gives you deliberate, stable control, but also means you’re responsible for revisiting it if the situation changes, since nothing automated is watching to correct it if it stops being the right choice.
25. How would you identify which queries Query Store considers “regressed,” and validate that finding before acting on it? The Regressed Queries report (portal) or a manual comparison of a query’s recent runtime statistics against an earlier baseline period using sys.query_store_runtime_stats directly shows this — but I’d validate by checking whether the regression correlates with a genuine plan change (not just natural variance in execution) and whether the “regressed” performance is actually consistent and sustained rather than one or two outlier executions skewing an average, before treating it as a confirmed regression worth acting on.
26. What’s the significance of Query Store’s runtime_stats_interval_start_time granularity, and how would you adjust it for different investigation needs? The default hourly aggregation interval is fine for general trend-spotting, but a genuinely fast-moving issue (a regression that develops and matters within minutes) might be smoothed out or hard to pinpoint precisely at hourly granularity — INTERVAL_LENGTH_MINUTES can be configured to a finer granularity for more precise timing, at the cost of more storage consumed for the same retention window, which is a trade-off worth being deliberate about rather than always maximizing granularity by default.
27. How would you use Query Store’s wait statistics view specifically, versus general system-wide wait stats (sys.dm_os_wait_stats)? System-wide wait stats give you an aggregate, cumulative-since-restart picture with no attribution to any specific query — useful for a general “what’s the system spending time on” view, but not for pinpointing a cause. Query Store’s wait stats tie specific wait categories directly to specific queries and time intervals, letting you directly answer “which query is actually causing these lock waits” rather than inferring it indirectly from a system-wide aggregate.
28. What’s your approach to using Query Store data to validate that an index change actually improved performance, rather than assuming it did? I’d compare the target query’s runtime statistics (duration, logical reads, CPU) for a representative period before the index change against a representative period after, using the same underlying Query Store data rather than a one-off manual test run, since Query Store’s aggregated real-execution data under genuine production load is a more trustworthy signal than a single ad hoc test execution that might not reflect real-world parameter variability or concurrency.
29. How does Query Store behave differently during a failover (planned or forced) — does the historical data carry over to the new primary? Since Query Store data is stored within the user database itself, and a synchronized secondary replica shares that same data, Query Store’s historical data generally carries over correctly through a planned (fully synchronized) failover. A forced failover, which can involve some data loss for very recent transactions, could in principle lose the most recent, not-yet-replicated Query Store data too, though the bulk of historical Query Store data would still be intact on the secondary that’s now primary.
30. What’s a good approach for using Query Store to build a genuine performance baseline before a major change (a migration, a version upgrade, a significant schema change)? I’d let Query Store accumulate a representative period of normal operation (at least covering a typical business cycle — daily and weekly patterns, not just a few hours) before the change, explicitly capture or export the relevant runtime statistics for key queries as a documented baseline, and then do a direct, query-by-query comparison against the same queries’ Query Store data after the change — rather than relying on a vague “it seems about the same” impression, which is exactly the kind of judgment Query Store is designed to replace with actual data.
31. How would you handle a scenario where Query Store shows a query has many different plans, but none of them stand out as clearly the “good” one? This often points toward a genuinely unstable or ambiguous optimization scenario — possibly a query whose “correct” plan legitimately varies a lot based on parameter values (making a single forced plan actually the wrong fix), or a case where the underlying issue isn’t really about plan choice at all but something more fundamental, like missing statistics or a schema design issue causing the optimizer to struggle regardless of which plan it picks. I’d resist forcing any specific plan reflexively in this case and instead dig into why the plans vary so much before deciding forcing is even the right tool.
32. What’s the operational overhead of Query Store, and how would you explain it to someone concerned about enabling it (or setting it to ALL capture mode) on a very busy production database? Query Store does add some overhead — capturing and persisting query and runtime statistics isn’t free — but it’s generally modest and specifically designed to be low-impact for its default configuration; the overhead becomes more noticeable specifically with ALL capture mode on a very high-query-volume system, or with an aggressively fine-grained interval setting. I’d frame it honestly: the default configuration’s overhead is a reasonable, usually worthwhile trade-off for the diagnostic value it provides, but ALL mode and very fine intervals are settings to apply thoughtfully and temporarily for focused investigation, not necessarily leave on permanently without reason.
33. How would you use Query Store to investigate whether a recent statistics update caused a performance change, positive or negative? I’d look at whether a query’s plan changed around the same timestamp as a known statistics update event (correlating with sys.query_store_plan‘s plan creation timestamps against known maintenance activity), and compare the runtime statistics immediately before and after that plan change — since a stats update triggering a recompilation and a genuinely different plan choice is a common, specific, and Query-Store-traceable cause of a sudden performance shift that might otherwise look mysterious.
34. What’s the relationship between Query Store and the missing index feature — does Query Store generate missing index recommendations itself? Missing index recommendations come from a separate DMV (sys.dm_db_missing_index_details), not Query Store directly — but Automatic Tuning’s CREATE INDEX option draws on both Query Store’s actual execution frequency/cost data and missing index information together, essentially using Query Store to add real-world weight and validation to what would otherwise be a purely estimate-based missing index suggestion.
35. How would you troubleshoot a scenario where a query you know is running frequently doesn’t appear in Query Store at all? Check the current QUERY_CAPTURE_MODE — if it’s set to AUTO (or a restrictive CUSTOM policy), a genuinely cheap, low-resource query might simply fall below the capture threshold and not get tracked, which is expected behavior, not a bug. I’d also verify Query Store’s actual operational state (sys.database_query_store_options) isn’t in a read-only or off state due to hitting its storage cap, which would explain missing data for an entirely different reason.
Advanced Level
36. Explain in detail how Query Store’s AUTO capture mode decides which queries to track, and the trade-offs of adjusting its underlying thresholds via CUSTOM mode. AUTO mode applies internal thresholds around execution count and resource consumption to filter out queries unlikely to be worth tracking — genuinely one-off, trivially cheap statements — reducing storage and overhead compared to capturing everything indiscriminately. CUSTOM mode lets you explicitly define the execution count and resource consumption thresholds yourself, which is useful when you have a specific reason to believe the default thresholds are excluding something relevant to your investigation (a moderately-frequent but individually cheap query that in aggregate matters), but it requires understanding your own workload’s actual query volume and cost distribution well enough to set meaningful thresholds rather than guessing.
37. How would you design a Query Store-based automated regression detection process, beyond just periodically checking the portal’s Regressed Queries report manually? I’d build a scheduled process (via Elastic Jobs or an external scheduler) that queries sys.query_store_runtime_stats directly, computing a rolling baseline (e.g., trailing 2-week average) per query and comparing recent performance against it with a defined statistical threshold for what counts as a meaningful regression (not just any variance), then alerting when a query crosses that threshold — essentially building your own automated version of what Automatic Tuning’s regression detection does internally, useful when you want visibility and alerting on the detection itself, independent of whether you also let Automatic Tuning act on it.
38. Explain how Query Store interacts with plan guides and query hints — does a forced plan via Query Store take precedence, and what happens if they conflict? A plan forced via Query Store (sp_query_store_force_plan) generally takes precedence over the optimizer’s natural compilation, similar in spirit to a plan guide, but they’re mechanistically distinct systems — Query Store’s forcing mechanism is more integrated and specifically tied to the tracked plan’s XML shape. If a query also has an explicit hint (like OPTION (RECOMPILE)) that would force fresh compilation, that combination can behave in ways worth explicitly testing rather than assuming — a RECOMPILE hint and a Query Store forced plan aren’t necessarily compatible, and it’s the kind of interaction I’d validate directly rather than reason about purely theoretically.
39. How would you approach diagnosing a scenario where Query Store shows a forced plan is still active, but the query is nonetheless performing poorly? First check whether the forcing itself actually succeeded and remains in effect (sys.query_store_plan.is_forced_plan and checking for a force_failure_count — plan forcing can fail under certain conditions, like a referenced index being dropped since the plan was captured), since a “forced” plan that’s silently failing to actually apply would explain this exact symptom. If forcing is genuinely successful and active, I’d reconsider whether the forced plan is actually still well-suited to current data volume and distribution, since data can grow or shift enough since the plan was originally captured that even the “forced good” plan is no longer actually good for current conditions.
40. What’s your approach to using Query Store data for capacity planning, beyond individual query tuning — trending overall workload growth over time? I’d aggregate Query Store’s runtime statistics at a broader level than individual queries — total execution count and total resource consumption trends across the whole captured workload over months, not just per-query — to build a genuine, evidence-based picture of overall workload growth, which is a more reliable input to a scaling or right-sizing decision than relying purely on point-in-time resource metric snapshots, since Query Store’s longer retention window (if configured generously) can reveal a genuine multi-month growth trend that a shorter metrics retention window might not fully capture.
41. Explain how you’d use Query Store data to build a defensible case for a specific schema or index change during a formal change-review process in a regulated environment. I’d present the actual before-state Query Store data for the affected queries — real execution counts, average duration, resource consumption — alongside the specific evidence supporting why the proposed change would help (a consistent full scan pattern, a specific plan operator that’s clearly the bottleneck), and frame the expected improvement in concrete, measurable terms that a post-change Query Store comparison could later validate or refute — giving the review process genuine, falsifiable evidence to evaluate, rather than a general assertion that the change “should help performance.”
42. How would you handle Query Store data retention and export requirements for a database where you need multi-year historical query performance trending, beyond what Query Store’s own retention window practically supports? Query Store’s own retention is meant for operational, relatively recent troubleshooting, not multi-year archival — for genuinely long-term trending, I’d build a periodic export process (via a scheduled job querying the catalog views and writing summarized results to a separate archival table or external store) capturing key aggregated metrics at a coarser granularity than Query Store’s native detail, specifically because trying to configure Query Store itself to retain years of full-fidelity data would be an inefficient and likely impractical use of the feature relative to a purpose-built export/archival approach.
43. What’s the diagnostic value of comparing Query Store data across a primary and a read-scale-out secondary replica, and what complications arise from that comparison? Since read-scale-out routes a genuinely different subset of traffic (reporting/analytical queries) to the secondary, comparing Query Store data between primary and secondary can reveal whether index or tuning decisions driven by primary-side data (which most Automatic Tuning activity is based on) are actually well-suited to the secondary’s distinct workload pattern — a complication worth knowing: Query Store data itself is shared as part of the database (not independently captured per-replica in a meaningfully separate way), so this comparison is more about which queries actually execute where via connection routing analysis than about Query Store maintaining genuinely separate datasets per replica.
44. How would you use Query Store to investigate a suspected memory grant issue affecting query performance under concurrency, given Query Store’s runtime statistics include memory grant data? sys.query_store_runtime_stats includes avg_query_max_used_memory and related columns, letting you compare a query’s granted versus actually used memory over time — a query consistently granted far more memory than it uses (or, conversely, one that would benefit from Memory Grant Feedback’s adjustment but isn’t seeing it stabilize) is visible directly in this data, giving a concrete, historical view of memory grant behavior rather than only being able to observe it live via sys.dm_exec_query_memory_grants during an active execution.
45. Explain a scenario where relying purely on Query Store’s aggregated statistics would mislead you, and what additional tooling you’d bring in to get the full picture. Aggregated statistics (like average duration) can mask genuinely bimodal performance — a query that runs in 50ms for one common parameter value and 5 seconds for another, averaging out to something that looks moderately concerning but not alarming, when the reality is a severe problem for a specific, important subset of executions. In that case, I’d bring in Extended Events with a targeted filter to capture individual execution details, including actual parameter values, since Query Store’s aggregation, valuable as it is, deliberately trades away that individual-execution granularity for efficiency, and sometimes you genuinely need it back for a specific investigation.
46. How would you design a Query Store-based approach to validate database performance consistency across a large fleet of similarly-provisioned databases (a multi-tenant SaaS scenario, for instance)? I’d build a fleet-wide comparison process querying each database’s Query Store data for a common set of representative query patterns (assuming reasonably similar schema/query shape across tenants), flagging tenants whose performance for equivalent queries deviates significantly from the fleet norm — since an individual tenant’s Query Store data alone doesn’t tell you whether their performance is “normal” without that cross-tenant comparison, which is specifically useful for catching a single problematic tenant database early, before it becomes a support escalation.
47. What’s your approach to using Query Store data as evidence during a post-incident review, specifically to establish an accurate timeline of when a problem actually began, versus when it was first reported? I’d query the specific affected query’s (or queries’) runtime statistics and plan history around and before the reported incident time, since it’s common for a regression to have actually begun measurably earlier than when it became noticeable enough for someone to report — establishing the true onset time accurately matters for correctly correlating the regression against other potential causes (a deployment, a data load, a configuration change) that might have occurred at the actual onset time rather than the later, more visible symptom time.
48. How would you handle the scenario where Query Store’s own overhead is suspected of being a genuine, non-trivial contributor to a performance problem on an extremely high-throughput database? I’d validate this suspicion directly rather than assuming — comparing overall database performance metrics with Query Store’s capture mode temporarily reduced (from ALL to AUTO, or interval granularity coarsened) against a baseline period, in a controlled way, since Query Store overhead being a genuine bottleneck is real but relatively uncommon at default settings; I’d want actual before/after evidence before making a change that reduces the diagnostic value Query Store provides, rather than assuming it’s the problem based on suspicion alone.
49. Explain how you’d approach migrating Query Store configuration and philosophy consistently across a fleet that includes a mix of Azure SQL Database, Managed Instance, and SQL Server on Azure VM (where Query Store isn’t on by default). I’d establish a consistent policy baseline (capture mode, retention, size cap) intended to apply everywhere, but explicitly verify actual enablement status per environment rather than assuming — Azure SQL Database and Managed Instance already have it on by default, so the real gap to close is specifically SQL Server on Azure VM instances, where it needs to be explicitly enabled and configured to match the same policy, since it’s easy for that IaaS-hosted subset of the fleet to be silently left out of an otherwise fleet-wide Query Store standardization effort if nobody specifically checks for it.
50. Walk through a genuinely difficult, real (or realistic) performance investigation where Query Store was the tool that ultimately found the root cause, end to end. Intentionally open-ended, because interviewers are evaluating your investigative process, not a memorized script. A strong structure to demonstrate regardless of the specific scenario: (1) start from the reported symptom and use Query Store to establish an objective, timestamped picture of what actually changed and when, rather than accepting a vague description at face value, (2) correlate that timing against other known events (deployments, data loads, statistics updates, platform maintenance) to form a specific, testable hypothesis about cause, (3) validate the hypothesis using the plan and runtime statistics data directly — confirming a plan change, a memory grant shift, a wait pattern change genuinely coincides with the regression, not just generally plausible, (4) apply the narrowest fix suggested by that confirmed root cause — forcing a plan, adding an index, addressing a stats issue — and (5) use Query Store again afterward to directly confirm the fix worked, comparing before/after data rather than assuming success. Interviewers consistently favor candidates who describe confirming their hypothesis with Query Store data before acting, and confirming the fix worked afterward with the same tool — that closed loop, more than knowing the catalog view names, is what the whole topic is really testing.
A Closing Thought
The idea running through nearly every advanced answer here: Query Store’s real power isn’t that it collects data — it’s that it turns performance troubleshooting from an exercise in memory and intuition into something you can actually prove, with a timestamp and a number attached. That shift, more than any single feature or view, is what separates a query store user from someone who genuinely relies on it.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
