The current situation we all face with the COVID-19 pandemic has made it difficult or impossible for many individuals to visit their favorite local stores. If the businesses are open, precautions need to be taken by both the proprietors and customers to ensure everyone’s safety. The hassles involved make it less enjoyable and potentially dangerous to do things we had previously taken for granted.
There is one store that should be of particular interest to SQL Server DBAs and that is open all the time. It’s the Query Store that was introduced by Microsoft in SQL Server 2016. It’s a great addition to the product that provides excellent performing tuning capabilities. When Query Store was first introduced, it was welcomed by SQL Server experts as an excellent feature that enables DBAs to gain a deeper understanding of how queries are running and which ones may need to be tuned to increase database performance.
What is the Query Store?
A query plan is a set of steps that a database management system, such as SQL Server, executes to complete a query. In SQL Server, query plans are also called execution plans. The Query Store provides insight into the choice and performance of SQL Server query plans. It accomplishes this feat by identifying the top resource consuming queries, furnishing a history of query plans, and finding resource consumption patterns that can offer important information regarding when specific queries become problematic.
One particular type of query that can cause performance issues is a regressed query. A regressed query is one that is no longer are optimized due to various factors like dropped indexes or outdated statistics. Query Store enables you to identify queries whose performance has degraded so they can be investigated and optimized.
Reports are available that help you stabilize performance by:
- Changing code or the schema;
- Adding a RECOMPILE to the query;
- Manually finding the best query in the cache;
- Using a plan guide;
- Forcing a plan to be used with Query Store.
Other reports display overall resource consumption of your SQL Server queries which can be studied to identify patterns that may be able to be modified to improve performance by using resources more efficiently.
Microsoft has built on the original functionality of the Query Store by adding features in SQL Server 2017 and 2019. In the 2017 version, the tool added wait states, automatic plan correction, and the ability to show more statistics about your queries. For SQL Server 2019, even more statistics are available to help address the performance of ad-hoc queries.
Getting the Most out of Query Store
An IDERA Webcast titled Intro to Query Store takes the viewer on a tour of the features of Query Store and provides tips on how to use it effectively. The webcast takes a deep dive into the benefits of using Query Store and includes many links to additional information that will help you use it in an effective way. If you plan to use Query Store with your SQL Servers, I highly recommend that you view this webcast.
The webcast concludes with a look at how you can use this feature with SQL Diagnostic Manager for SQL Server. This flexible monitoring tool can pull data generated from Query Store and provides a vehicle for creating charts and reports to facilitate performance tuning. You can even use the application to determine if the use of Query Store is impacting database performance. Through the use of Query Store and SQL Diagnostic Manager for SQL Server, you can optimize your queries and improve overall database performance.