Tuning Workload Performance with Query Store

Image result for Tuning Workload Performance with Query Store

As your database grows, it is not unusual to experience performance problems caused by queries that once performed well, but now perform poorly. Without the ability to see all the changes in the query execution over time, it is difficult to figure out why regressions happen and what you can do to prevent them. Troubleshooting these performance issues can take hours or even days.

That’s exactly what Query Store, a new feature available in Azure SQL Database and SQL Server 2016, aims to improve.

Similar to an airplane’s flight data recorder, Query Store constantly collects information about all queries and greatly simplifies performance forensics by reducing the time to diagnose and resolve issues. Query Store also allows you to force query plans from the history, which makes the process of fixing problems with plan choice regressions extremely easy.

 

As a result, time to detect and mitigate performance issues is now in the range of minutes.

Typical usage scenarios

Query Store can be used in a wide set of scenarios when tracking and ensuring predictable workload performance is critical. It is equally useful when you need to react to an immediate problem as well as when you want to ensure optimal performance proactively for the long term (after the database upgrade, for other maintenance operations, or upon new application roll-out, for example).

 

 

[Source: Blogs Technet]