where to use query store and what type of options are available in query store?

Problem: where to use query store and what type of options are available in query store?

Solution:

Starting from SQL Server 2016, Query store is a feature that is available in Standard Edition.  This feature makes our life easy when trouble shooting, if you don’t know where to look for in terms of performance or bottlenecks of a query or a stored procedure.

To Read more about what query store is, please go through this article.

More about Query Store

Once we have query store enabled there would be certain features that comes handy in order help for trouble shooting a query.

So basically, this would be the process.

1.     Enable Query Store on a database.

2.     Execute the query on the database.

3.     Let Query store collect the statistics.

4.     Filter the query and pin point the execution plan details.

5.     Get Suggested plans from query store.

As we can see the option from the above screen shot there are four options.

1.     Regressed Queries - Use this view to correlate observed performance problems in your application with the actual queries that needs to be fixed or improved.

2.     Top Resource Consuming Queries - Use this view to focus your attention on the most relevant queries which have the biggest impact to database resource consumption.

3.     Tracked Queries -  Use this view when you have queries with forced plans and you want to make sure that query performance is stable.

4.     Overall resource consumption - Use this view to identify resource patterns (daily vs. nightly workloads) and optimize overall consumption for your database.

Example:

In this example, I would like to show how we can use the top 25 performance consuming queries using the query store and get recommended suggestions from the query store.

OUTPUT:

Step 1: open the query store on the database folder and right click to get the available options.

Step 2: choose top resource consuming queries

Step 3: choose to select the query to get force plans.

The Query store configurations are shown on the top right corner as shown in the picture of a database. As you see below.