With the newest version of SQL Server, a new feature named the “Query Store” can help you diagnose performance problems that are related to plan changes. The Query Store in SQL Server 2016 is described as a service that monitors query plans with a full history of query execution. It is unclear of the full feature will be available in the Standard edition, but it will definitely be available as part of the Enterprise edition.
You can read more about what it does in this article by Joey D’Antoni, we find an example of how you might use this new feature, and why that makes it so awesome.
I got an email from one of my clients (these guys are brave and already live on 2016—they along with Microsoft have that much confidence in the code) about a query that was running poorly over the weekend (worse than in the older environment). A little bit about their environment—it’s largely a data warehouse type solution, with the goal of delivering data sets to their clients. In the upgrade to 2016, we did a rearchitecture that heavily leveraged clustered columnstore indexes, and took advantage of Availability Groups for scale out reads. So remember when one of your customers would email you about something that was slow over the weekend, and you would desperately scour the plan cache, possibly writing xQuery to look for the needle in the haystack of a query that was performing poorly?
Enter the Query Store. I opened up the database, and find the Query Store in Object Explorer.