Why The SQL Server Query Store is Awesome

SQL Server

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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s