What is Query Store: SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes.
Why I have to use Query Store: Quey store automatically capture a history of queries, plan, and runtime statistics, and retain these for your review. well if you want to choose the hard path to solve performance issues then don't use query store.
How to use Query Store: Well I like this question, I will try to explain whatever I understood.
1. Go to SQL Server Mangement Studio
2. Object Explorer, right-click on a database, and select properties
3. From Database Properties window select Query Store Page
4. From Operation Mode( Requested) Select read write or read only
*** you cannot enable Query Store for master and tempdb
After enabling Query store, go to required Database --> Query Store
Query Store will log information about each query including:
1. Number of executions
2. execution time
3. Memory consumption
4. Logical Reads
5. Logical Writes
6. Physical Reads
7. Number of execution plan changes
To reduce the load on the server, this information is aggregated into a fixed window. If you need more precise data, you should look to Extended Events.
Now open regressed queries view. you will see a similer window like below.
This tool will allow you to see regressions based on any of the recorded metrics. If you see a regression, you have the option to force SQL Server to use an older execution plan.
No comments:
Post a Comment