Another enhancement added to SQL Server 2016 is the Query Store. As I was reading about this feature I thought, “how really cool this is.” One of the SQL tricks I have used in the past is to maintain my own tables, either in tempdb, or elsewhere, with a history of query performance statistics. This allows me to track how an individual query is performing over time. I can also determine in real time statistics, how my optimization attempts are really working.
Well, now you have this capability built into the sql engine itself. The query store feature tracks a history of query plans, and performance statistics, for individual queries. Right out of the box this is a real win.
The best part about it is that it will perform much faster than a home grown implementation. The biggest issue I had when running a home grown version of query performance tracking was the load on the database engine. I want to use as much of the engine to do work, rather than track work to be done.
Because of the history, you have to ability to roll back to using an older plan that performed better historically. What’s even more cool is the ability to transfer this query store to another instance of SQL and start out with an already warmed up set of query plans, increasing your performance when a database is freshly mounted. Think of the value here when moving a database from one host to another. This can reduce the perceived amount of time needed to transfer hosting of a database from one server to another.
Cheers,
Ben