Free tools Windows power users keep installed
One-click scans. No signup required.
Use Query Store to investigate how query performance and execution plans changed over time. Use Extended Events to capture selected SQL Server events and their details during a specific scenario. If a plan regression leads you to investigate a particular engine event, the two tools can complement each other.
How to choose
| Your question | Start with | Why |
|---|---|---|
| Which queries or plans became slower during a time window? | Query Store | It retains query, plan, and runtime-statistics history organized into time windows, allowing you to compare performance across the periods it captured. Microsoft’s Query Store overview |
| Did a particular engine event occur, and what details did it include? | Extended Events | A session collects selected events and sends their data to a configured target. What you can inspect depends on the events, filters, session state, and target you chose. Microsoft’s Extended Events quickstart |
| Did Query Store plan forcing fail? | Query Store and Extended Events | Query Store can help identify the plan issue; Microsoft documents the query_store_plan_forcing_failed Extended Event for tracking plan-forcing failures. Microsoft’s Query Store overview |
What each feature tells you
Query Store: history of query performance
Query Store records query text, execution plans, and runtime statistics, then groups runtime data into time intervals. On supported versions, it can also retain wait statistics. This makes it useful for retrospective questions such as “This query used to be faster” or “Which plan changed overnight?” You can compare the retained metrics and plans for the relevant periods, and, where appropriate, force a previous plan that performed better. Microsoft documents Query Store’s performance-monitoring capabilities; wait-statistics support depends on version and service.
As an Amazon Associate I earn from qualifying purchases.
Extended Events: selected event details
Extended Events uses sessions to collect database-engine events. You decide which events to capture, whether to filter them, and where collected data goes. It is better suited to a focused question about an event than to comparing a query’s aggregate performance across retained time windows. A session cannot tell you about events it was not configured to capture while it was running. Microsoft’s quickstart explains sessions, events, and targets.
Investigate a query that got slower
- Check Query Store status for the affected database. Confirm it was enabled and operating in a mode that collected data during the time you need. Do not assume it was on: Microsoft says Query Store is not enabled by default on SQL Server 2016, 2017, and 2019, while new SQL Server 2022 databases have it enabled by default in read-write mode. Defaults can differ across releases and services. See Microsoft’s version and service guidance.
- Confirm the period is covered. Check that the relevant data has not been removed by cleanup and that the configured retention and storage settings allowed it to remain. If Query Store was disabled or the period has been cleaned up, it cannot provide that missing history. Microsoft’s Query Store workload best practices and management guidance cover capture and retention considerations.
- Compare query metrics and plans across the relevant intervals. Look for a change that coincides with the slowdown. Query Store data becomes available as it is collected, but Microsoft notes that a representative picture of a workload may require enough time to accumulate.
- Decide whether the next question is about a specific event. If the plan comparison points to a problem that requires event-level evidence, configure a targeted Extended Events session for the event and context you need. If plan forcing is involved, Microsoft documents
query_store_plan_forcing_failedfor tracking failures.
Set up collection with the right scope
Query Store checks
- Review its actual operation mode and capture configuration rather than relying on release defaults.
- Check the storage limit and cleanup policy so useful history remains available.
- Allow collection to run long enough to represent the workload you want to assess; begin examining data as it arrives, but account for the time needed to build a representative view.
Microsoft’s workload best practices and management best practices describe these settings.
#1 Best Overall
Extended Events checks
- Choose the event, filters, and target to match the incident. Keep the session focused on the question rather than collecting broadly without a reason.
- Verify the session is running when the event occurs and that you can access its target afterward.
- Plan permissions before creating or viewing a session. Microsoft’s quickstart lists
CREATE ANY EVENT SESSIONfor SQL Server 2022 and later, orALTER ANY EVENT SESSION, as requirements for creating sessions; it describesVIEW SERVER PERFORMANCE STATEfor viewing sessions through SQL Server Management Studio. - For Azure SQL Database, Azure SQL Managed Instance, and Fabric SQL database, the quickstart says event files are stored in Azure Storage and an Azure storage account is needed. Follow the guidance for the exact service because setup and permissions differ.
Check Microsoft’s Extended Events quickstart for environment-specific details. Extended Events is described by Microsoft as a lightweight performance-monitoring feature, but an active trace can still contribute CPU overhead depending on the events selected. Microsoft’s slow-server troubleshooting guidance discusses this trade-off; no single overhead figure applies to every session.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Version and service differences matter
Query Store applies to SQL Server 2016 and later and to named Azure services, but availability, defaults, and capabilities vary. Wait-statistics dimensions are documented starting with SQL Server 2017 and Azure SQL Database; do not assume the same evidence is available on every SQL Server version or cloud service. Verify the documentation and settings for the specific database you’re troubleshooting. Query Store version guidance and wait-statistics documentation provide the relevant qualifications.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
