Query Store Configuration Optimization for SQL Server

Manage Query Store properties to improve its performance

Optimizing the properties of the Query Store in SQL Server is important because it enhances database performance, supports efficient query execution, and improves system resource utilization. The Query Store captures, retains, and analyzes query execution plans and runtime data to identify suboptimal or resource-intensive queries. By configuring Query Store properties, such as data retention and capture modes, administrators can strike a balance between monitoring query performance and minimizing the storage footprint. As a result, this optimization allows them to identify and address code-related bottlenecks, resolve performance issues, and fine-tune query execution strategies, ultimately leading to a more streamlined, efficient, and responsive data management system for the organization.

The free tool SQL Query Store Optimizer optimizes the configuration of the Query Store of Microsoft SQL Server to better analyze execution plans, performance changes, and inefficient queries to enable optimal query performance. It help you to:

• Track performance history and troubleshooting of query plan-related issues.
• Discover meaningful insights in query performance without affecting database performance.
• Choose what data gets collected and stored, affecting the quality and quantity of data available for analysis.
• Ensure dependable and consistent results while upgrading to a newer SQL Server or when conducting before/after testing.
• Deploy the free tool without installing anything on the affected database instances.

Optimize SQL Server’s Query Store to Improve Performance

Troubleshoot issues and execute tasks that follow best practices to help keep Query Store tuned to the database workload. Improve the performance of SQL Server’s Query Store by optimizing its properties and contents. This affects how the Query Store captures per database a history of queries, plans, and run-time statistics.

See all Query Store Properties in Single Summary Table

Register one or more SQL Server instances. For a selected connected instance, display all database Query Store properties in a single summary table. This provides an overview of all of the Query Store properties to compare properties across Query Stores, to quickly identify the origin of performance problems, and to quickly select a Query Store to optimize its performance.

Execute Predefined Actions that Modify Query Store Properties and Content

For a selected database, provide predefined Query Store actions including:

• Clear Query Store contents
• Restart Query Store
• Recover from memory corruption
• Recover from disk corruption
• Set Query Store to default settings
• Keep only most relevant data in Query Store
• Flush Query Store in-memory cache to disk
• Find and delete ad-hoc queries executed only once and older than 24 hours

Edit All Query Store Properties in Single Overview Panel

For a selected database, display all of the twelve properties of its Query Store in a single overview panel, and allow editing of all eight modifiable properties. Together with the summary table, this allows for quick selection of Query Stores and optimizing their performance. In contrast, SQL Server Management Studio (SSMS) provides access only to a subset of eight properties, requires vertical scrolling to access the last two properties, does not display default values, does not provide more involved actions to adjust properties and contents, and is not efficient for more than just a few servers, instances, and databases. Access to additional properties and more involved actions are only available as T-SQL snippets mentioned on several web pages.

No Agents Required on Managed Instances

Installing agents on SQL Server instances can be tedious and invasive. And agents installed on monitored SQL Server instances can impact performance. SQL Query Store Optimizer does not require installing an agent on the managed instances.

Connect to the Cloud and Run In the Cloud (Provisional)

Connect to database instances hosted on cloud virtual machines, such as SQL Server on Azure Virtual Machine (VM) and SQL Server on Amazon Elastic Compute Cloud (EC2).

Install and run on virtual machines hosted in the cloud, such as Windows on Azure Virtual Machine (VM) and Windows on Amazon Elastic Compute Cloud (EC2).