Query Store in SQL Server 2017

Query Store in SQL Server 2017

Performance Tuning with Query Store in SQL Server 2017

If you want to monitor the performance of your queries within SQL Server 2017, you can with a feature called Query Store. Query Store monitors changes to your queries to see if performance deteriorates or improves when the query plan changes.
When you create a new database,  is disabled by default; you need to enable this feature.

To enable Query Store, do the following steps
1. In Object Explorer, right-click a database, and then click Properties.
2. In the Database Properties dialogue box, select the Query Store page.
3. In the Operation Mode (Requested) box, select On.

You would use Query Store when you want to
• Find queries that have become slow over time and fix them by forcing the queries to use a different query plan.
• Find out how many times a query was run in a set period.
• Find out how many times the query plan was changed.
• Analysis why your query plan changes.

The best way to learn about

is to play around with it so just enable it and refresh the database, so it looks like this

 

Query Store in SQL Server 2017
Query Store in SQL Server 2017

Then just double click on each item in the query store to see what they do. It is better to enable Query Store on a database that has many transactions so you will be able to see results appear fast. I would suggest using a development server to test this feature as turning on this feature can have a minor effect on performance on your server.

 

If you have any comments on this article, just leave a comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *