The Query Store is a feature that helps you evaluating query performance. It captures execution information asl well as up to 200 plans for a single query. For a thorough overview on the Query Store you can read Enrico van de Laar’s article here. Query Store is also available for Azure since V12. To turn on Query Store you would run the following command:
ALTER DATABASE <database_name> SET QUERY_STORE = ON;
You can also use the GUI in SQL Server to turn on Query Store:
I learned the following neat trick last week when I attended SQL Saturday. We know upgrading between SQL Server versions may lead to slow queries, as our queries are optimized for, well, the SQL Server we use. That means if your production server is a 2008 R2, and you upgrade to a 2016, you might end up with a few queries that in fact execute slower than what you are used to. If you are a smart DBA (and you are a smart DBA), you put a few test databases on the target server and let them run for a while. You evaluate performance, growth, make some changes, and when everything looks peachy you move on to upgrade your servers.
This is where the Query Store can help you in that process. Say, I want to upgrade from my current 2008R2 server. I would grab a database from my current 2008R2 server, restore it on the 2016 server, but leave the compatibility level at 2008R2. I turn on Query Store here, and allow it to capture all execution plans for all queries. These data will stay in the memory for 900 seconds by definition, then will be flushed to disk. You hear that right — Query Store saves all this information to disk. When I restart the server, the execution plans are still going to be there for me. I will let that database happily spin around for, say, a month. I can always check on its progress by accessing Query Store in SSMS.
At that time I change the compatibility mode to SQL Server 2016. What do you think will happen? You guessed it right, nothing will change for the most part, except for a few queries that would suddenly run slower. Way slower. I have heard stories of queries that used to run in mere seconds on 2008R2, but would take up to 15 minutes on 2016. That is because the Cardinality Estimator has undergone major changes over the past few years, especially with the introduction of SQL Server 2014.
At this point I turn to my new best friend, Query Store, and look at the executions plans it saved for that particular query. I would see something like this:
I can see that plan 146 was the best performing execution plan. I can now select that plan and click the ‘Force Plan’ button right under the scatter plot.
I would do this to all my slow-performing queries. Tah-dah! I now crossed off one item on my ‘upgrade to SQL Server 2016’ list, and I don’t have to worry about execution plans anymore. Isn’t that neat?!
For more information on the Query Store, I recommend Microsoft’s surprisingly reader-friendly page here, as well as the aforementioned Enrico van de Laar’s extensive article over on Simple Talk. Credit goes to these two articles for their images as well.