Sometimes, you will notice that perfectly fine queries will show weak performance for no particular reason. One of the reasons for this could be a change of the execution plan. An execution plan is an internal result SQL Server procedure where the engine needs to decide the best possible way to execute your query. Each query has its own plan. During standard database operations, based on internal statistics, those plans can be changed, but the results are not always better in terms of performance. On the contrary, they can be the exact opposite. Before SQL Server 2016, it was not an easy task to investigate these problems.

You were facing the following issues:

The main reason for this is that the fixing query plan that causes regression is a difficult task to perform. The query plan is not designed for performance troubleshooting. Fortunately, we have this great feature. Query store provides you with insight on the query...