Manage Query Store properties to improve its performance

SQL Server’s Query Optimizer analyzes SQL statements to determine the most efficient method to produce the requested data. Such a SQL query optimizer refers to the query, the database schema, and the database statistics. The SQL query optimization tool then creates different query execution plans. From these plans, the SQL optimizer then selects the least costly option. The SQL optimization tool then recommends that the Database Engine executes the selected plan.

SQL Server’s Query Store, since its introduction with SQL Server 2016, is becoming better utilized by database administrators to review the history and performance of SQL queries. The Query Store collects useful information for SQL queries such as run time statistics and execution plans for analysis and reporting. As such, the Query Store can help to improve the performance of SQL queries.

IDERA’s free tool SQL Query Store Optimizer provides an intuitive graphical user interface to inspect and modify the configuration of the Query Store.

• Improve SQL Server 2016 Query Store performance
• See all Query Store properties in single summary table
• Execute predefined actions that modify Query Store properties and contents
• Edit all modifiable Query Store properties in single overview panel
• Get up and running in minutes with no agents

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

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 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, 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.

Get up and Running in Minutes with No Agents

The small download and easy installation wizard will get you up and running quickly. No agents need to be on the server.

* Optimize SQL Server on-premises and in the cloud, and without installing anything on the optimized instances.