This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
|
1 |
ALTER DATABASE Database_Name SET QUERY_STORE = ON; |
You can use below query to find an information on Query Store:
|
1 2 3 4 5 6 |
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; |
