A SQL Server feature that automatically captures query execution plans and runtime performance statistics, providing DBAs with a historical record to identify regressions, force stable plans, and track query performance over time.
- Data collection: Query Store collects query text, execution plans, and runtime statistics such as duration, CPU usage, I/O, and memory in system tables within the database, preserving this information across SQL Server restarts.
- Plan regression detection: The primary use case for Query Store is identifying plan regressions and forcing stable execution plans. When a query slows down because of a new execution plan, DBAs can force the last known good plan.
- Production recommendation: Query Store is recommended for all production databases running SQL Server 2016 and later, as it provides valuable historical context during performance investigations.
- Automatic Plan Correction: Available in SQL Server 2017 and later, Automatic Plan Correction can detect plan regressions and automatically revert to better-performing execution plans without DBA intervention.
- Performance analysis: DBAs use built-in Query Store reports, including Top Resource Consuming Queries, Regressed Queries, and Overall Resource Consumption, to proactively monitor and optimize database performance.
- Relevant Idera tools: SQL Diagnostic Manager integrates with Query Store data to surface plan regression alerts and provide historical query performance insights.
- Related terms: Execution Plan, Parameter Sniffing, Performance Monitoring, Dynamic SQL.
