SQL Server’s Query Store can accumulate hundreds or even thousands of queries over time. When troubleshooting a specific performance issue or investigating a particular query, you’ll probably want to search the captured data to find what you’re looking for.
Query Store
How to Check Query Store Status and Configuration in SQL Server
SQL Server’s Query Store operates independently on each database and can be in different states depending on its configuration and current storage usage. Checking the status helps you verify that Query Store is working as expected and identify any issues that might prevent data collection.
How to Exclude Specific Queries from Query Store in SQL Server
SQL Server’s Query Store captures all queries by default (when using QUERY_CAPTURE_MODE = ALL) or resource-intensive queries (when using AUTO). However, you may want to exclude certain queries from being tracked. These might include monitoring queries, maintenance scripts, or queries from specific applications that add noise to your performance data.
SQL Server doesn’t provide a built-in mechanism to exclude specific queries from Query Store by query text or pattern. But you can achieve similar results through several approaches.
Monitoring Query Store Storage Usage in SQL Server (T-SQL)
When you have Query Store enabled in SQL Server, it consumes disk space in order to store query text, execution plans, and runtime statistics. Monitoring storage usage can help you avoid situations where Query Store fills up and stops capturing data or switches to read-only mode.
How to View Query Execution History Over Time in SQL Server
In SQL Server, Query Store aggregates performance statistics into time intervals, allowing you to see how query performance changes over time. This historical view helps you identify when performance degraded, spot patterns like daily or hourly spikes, and correlate performance changes with deployments or data changes.
This assumes that Query Store is enabled on the database in question. So assuming Query Store is enabled on the database, you can use the following queries to check the execution history of queries over time.
How to View All Queries in Query Store in SQL Server
SQL Server’s Query Store captures all executed queries (or a subset based on your capture mode) along with their execution statistics. Viewing the complete list of captured queries helps you understand what’s been tracked and provides a starting point for performance analysis.
How to Unforce a Query Execution Plan in SQL Server
When you’ve forced an execution plan in SQL Server’s query store and later want to allow the optimizer to choose plans freely again, you’ll need to unforce the plan. You might want to do this because you’ve fixed the underlying issue or the forced plan is no longer optimal. Whatever the reason, the solution is straightforward and easy.
How to Clear Query Store Data in SQL Server
SQL Server’s Query Store accumulates query execution history, plans, and runtime statistics over time. Eventually you may need to remove this data to free up space, start fresh after troubleshooting, or clear out information that’s no longer relevant. Fortunately, you can clear Query Store data without disabling the feature entirely.
How to Force a Query Execution Plan in SQL Server
When the optimizer consistently chooses a poor execution plan for a query, you can force SQL Server to use a specific better-performing plan from Query Store. This can provide immediate relief while you investigate and fix the root cause of the poor plan choice.
List of Query Store Configuration Options in SQL Server
SQL Server’s Query Store provides a handy way to track query performance over time, making it easier to troubleshoot issues and optimize workloads. To get the most out of it, it helps to understand the various configuration options that control how it collects, stores, and manages data.
