Query Store
A Query Store will store the multiple execution plans of the same query. And, You and I can analyze different execution plans for the same query, choose the efficient one and force query to use plan. Now, You can quickly recall a procedure cache. Yes, procedure cache also stores the execution plans but it stores the latest execution plan only. You and I also need to remember that these plans stored in the Procedure Cache will be wiped out due to memory pressure which is not the case with the Query Store.
How to enable Query Store
there two ways to enable query Store
1.By Script
ALTER DATABASE database_name SET QUERY_STORE = ON;
2.Manually
Right Click on the database on which you want to enable the query store
On the left select Query Store Change the Operation Mode from Off to Read Write.
To confirm if the Query Store is on
you can use the following command :
Select * from sys.database_query_store_options
OR
You can see if it is present under your database in the dropdown below Programmability
How to plan of one query on another query
USER TABLE
I have created this stored procedure as on the above table which as 299398 rows of data :
Create Procedure userbypoints
@Points int
As
SELECT * FROM [dbo].[Users]
WHERE Points=@Points
Go
Executed it with two different parameters 1 and 2
Exec userbypoints @points=2
Exec userbypoints @points=1
Below is the view of Top Resource consuming Queries
In the following list of queries which are most resource-consuming
In the list, userbypoints stored procedure is at the top there two different plan at right, we can two dots in the graph which show represent the different plan used for the execution
There are two plans for userbypoints with Plan id 13 and Plan id 22
This is the graphical view of the queries which shows the query and duration required to execute
Click on Plan id which you want to force on another and then Click on the execution plan symbol shown below in the red box in the plan summary
OR
Execute the following command to force plan
Exec sp_query_store_force_Plan @query_id= 13,Plan_id= 22;
The tick on the plan indicates the plan is forced successfully
