SQL Server 2016 introduced a Query Store concept which helps us to monitor the performance differences between the query execution plans.
It helps you to track the query plan, run time statistics and history of query plan.
You can easily find your new query with the different execution plans and you can identify un-efficient plan and force for the better execution plan.
Once you enable the Query Store, It starts to capture a history of queries, plans and other run time statistics.
You can audit the query plans and can find information like: top n queries, CPU/IO/Memory utilization of queries and query executed in a number of times.
Enable the Query Store:
ALTER DATABASE Database_Name SET QUERY_STORE = ON;
You can use below query to find an information on Query Store:
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
JOIN sys.query_store_query AS Qry
ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt
ON Qry.query_text_id = Txt.query_text_id;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.