7 questions found
What is Query Store in SQL Server, and what information does it automatically capture?
Beginner
Query Store is a built-in feature that automatically captures a history of queries, their execution plans, and detailed runtime statistics like duration and resource usage over time, giving you a persistent record you can analyze later without needing to have already been actively monitoring the server when a performance problem occurred.
ALTER DATABASE MyDatabase SET QUERY_STORE = ON;
Real-world example
A database administrator enables Query Store on a critical production database, immediately gaining visibility into query performance history that will be invaluable the next time a performance issue needs to be investigated.
Common follow-ups: Is Query Store enabled by default on new databases?;How much overhead does enabling Query Store typically add to a system?
Query Optimization & Plans;SQL Server Profiler & Extended Events
How do you view the top resource consuming queries captured by Query Store?
Beginner
You can use the built-in reports available directly in SQL Server Management Studio under the Query Store folder for a database, such as Top Resource Consuming Queries, which visually shows you which queries are using the most CPU, duration, or memory over a selected time period, without needing to write any custom queries yourself.
-- Query Store reports are available in SSMS
-- under Database > Query Store > Top Resource Consuming Queries
Real-world example
A database administrator opens the Top Resource Consuming Queries report in SQL Server Management Studio and immediately identifies a single report query responsible for the majority of CPU usage during peak business hours.
Common follow-ups: What other built-in Query Store reports are available besides top resource consuming queries?;Can these reports be customized to focus on a specific time range?
SQL Server Profiler & Extended Events;Query Optimization & Plans
How do you use Query Store to identify a query whose performance has regressed after a recent code deployment?
Intermediate
You use the Query Store's Regressed Queries report, which compares a query's recent performance against an earlier baseline period, highlighting queries that have become noticeably slower, letting you quickly correlate a performance drop with a specific deployment or data change rather than manually searching through logs.
-- The Regressed Queries report compares two time periods
-- automatically highlighting queries that have gotten slower
Real-world example
A team deploys a new feature and later notices overall application slowness, using the Query Store Regressed Queries report to pinpoint the exact query that started performing significantly worse right after the deployment.
Common follow-ups: How do you configure the specific baseline and recent time periods for this comparison?;What should you do once a regressed query has been identified?
Query Optimization & Plans;Deployment
How do you force SQL Server to use a specific, previously known good execution plan for a query using Query Store?
Intermediate
You identify the desired plan id from Query Store's history of captured plans for that query, and use the sp_query_store_force_plan stored procedure to instruct SQL Server to always use that specific plan going forward, which is especially useful for quickly stabilizing performance after a plan regression while you investigate the underlying cause.
EXEC sp_query_store_force_plan
@query_id = 42,
@plan_id = 100;
Real-world example
A database administrator forces a previously fast execution plan for a critical report query after Query Store revealed the optimizer had recently switched to a much slower plan, immediately restoring good performance while the root cause is investigated.
Common follow-ups: How do you later unforce a plan once the underlying issue has been fixed?;Does forcing a plan guarantee it will always be used regardless of future data changes?
Query Optimization & Plans;Stored Procedures & Functions
How would you configure Query Store's capture and retention settings to balance detailed monitoring with storage overhead on a very busy production database?
Advanced
You would adjust settings such as the maximum size allocated for Query Store data, the data flush interval controlling how often captured information is written to disk, the statistics collection interval controlling the granularity of captured metrics, and the retention period, tuning these based on how much history you actually need versus the storage and overhead you are willing to accept.
ALTER DATABASE MyDatabase SET QUERY_STORE (
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 15,
STALE_QUERY_THRESHOLD_DAYS = 30
);
Real-world example
A busy e-commerce platform carefully tunes its Query Store settings, balancing a reasonable thirty day retention period against a defined storage limit, ensuring they have enough history for troubleshooting without letting Query Store data consume excessive disk space.
Common follow-ups: What happens when Query Store reaches its configured maximum storage size?;How do you monitor how much storage Query Store is currently consuming?
SQL Server Architecture & Editions;Backup & Recovery
How can Query Store data be queried directly using its underlying system views for custom analysis beyond the built-in reports?
Advanced
Query Store exposes several system views such as sys.query_store_query, sys.query_store_plan, and sys.query_store_runtime_stats, which you can join together to write fully custom queries analyzing exactly the metrics and time periods you care about, giving you more flexibility than the built-in reports alone for building specialized dashboards or automated alerts.
SELECT qt.query_sql_text, rs.avg_duration, rs.avg_cpu_time
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;
Real-world example
A performance engineering team builds a custom automated alert that queries Query Store's system views directly, notifying the team whenever a query's average duration exceeds a defined threshold, going beyond what the built-in reports alone could provide.
Common follow-ups: What other useful system views does Query Store expose for custom analysis?;How often should custom Query Store queries like this be run in an automated monitoring process?
SQL Server Profiler & Extended Events;Query Optimization & Plans
What is the difference between Query Store's READ_WRITE and READ_ONLY operational modes, and when does SQL Server switch between them?
Intermediate
In READ_WRITE mode, Query Store actively captures new query and plan information as queries run, while in READ_ONLY mode it stops capturing new data but still allows you to view previously captured history, which SQL Server automatically switches to if Query Store reaches its configured maximum storage size, requiring you to either increase the size limit or clean up old data to resume active capturing.
-- Check the current operational mode
SELECT actual_state_desc FROM sys.database_query_store_options;
Real-world example
A database administrator notices Query Store has silently stopped capturing new data and discovers it switched to READ_ONLY mode after reaching its storage limit, prompting them to increase the allocated size and clean up old, unnecessary data.
Common follow-ups: How do you manually switch Query Store back to READ_WRITE mode?;What causes Query Store to silently stop capturing data without an obvious error?
SQL Server Architecture & Editions;Backup & Recovery