Query Store

Query Store — introduced in SQL Server 2016—is a powerful feature that tracks query performance over time. It acts like a
flight data recorder, collecting detailed execution plans, query runtime statistics, and resource usage metrics for all queries. By capturing this telemetry, it enables DBAs to identify performance degradation, regressions, and plan changes before they impact users.

How it works:

• Once enabled at the database level, Query Store periodically samples and archives runtime metrics (CPU, duration, I/O)
  and execution plan details.
• It correlates individual query text with the plans used to execute them.
• Data is stored in internal tables, separate from user tables, and retained based on size and time policies you
  configure.

Key features:

• Regression detection: track when a query’s plan changes and causes a spike in duration or CPU.
• Forced plans: if newer plans are less efficient, you can force a previously better plan to be used.
• Performance tuning insights: explore top resource-consuming queries, worst regressions, and stats by time or plan.
• Built-in reports in SSMS: visualize top resource consumers, regressions over time, regressed queries, and CPU/IO trends.
• Baseline comparisons: retrieve performance metrics from past periods for baseline analysis.

Usage tips:

• Enable Query Store in production environments—but monitor growth and purge older records to avoid storage bloat.
• Combine with Index Tuning and Performance Monitor for comprehensive diagnostics.
• Use Database Scoped Configurations to set capture modes (e.g., auto, all, none).

Overall, Query Store empowers DBAs with historical query performance insights and plan control tools, reducing the
guesswork in troubleshooting and tuning.