Query Store – the best thing for performance tuning since Extended Events

To understand what makes the Query Store such a revolution, a little background on SQL’s built-in performance monitoring is necessary. To start with, by default, there are no traces or event sessions that record query performance. The argument has always been that the overhead is unacceptable. The only traces and event sessions that are enabled by default are a default trace and a very light-weight system health extended events session. SQL tracks query performance stats but only in an aggregated form. Aggregated since the query plan was cached, which for a frequently run query could be just after the server started. Additionally, these performance stats are not persisted, so if SQL is restarted (as too often happens when there’s a performance-related incident), they are all lost.

If someone asks me to help them to identify the cause of a performance problem that they had the previous day, at the moment the answer would be, ‘There’s no way to do so.’ Unless the person had traces or extended events sessions running, third-party monitoring tools or custom benchmarks, there won’t be enough information available a day later to identify the cause of performance problems. However, with the Query Store, SQL automatically tracks the run-time performance characteristics of queries, including CPU usage, duration, reads, row count, degree of parallelism and memory usage. These stats are stored per statement and object/batch and persisted within the database, aggregated over a time interval chosen by the DBA, defaulting to one hour.

The Query Store includes the same query hash that we have had in the query stats DMVs since SQL 2008, allowing easy identification of ‘similar’ queries – queries that have the same form but differ by embedded constants. This is fantastic: it means that when the previously mentioned scenario occurs, the answer is no longer that there’s no way to do so, but rather, ‘Can you get me a backup of the DB from after that performance problem?’ or if backups cannot be provided, ‘Can you run these queries and send me the results?’ It means that it is possible to diagnose the cause of performance problems days, weeks, even months after the incident occurred.

If that is not enough, the Query Store allows for the forcing of a plan. Not using complex WITH PLAN hints or by creating plan guides, but simply by associating a query ID, from the Query Store, with a plan ID, also from the Query Store. The selected plan is then forced for that query until the association is removed. Forcing plans is still something of a last resort for query tuning, but at least now it’s easier to do.

The Query Store, when it is available, will be a massive improvement in the area of performance tuning for SQL databases. It alone would make upgrading to the next version of SQL worthwhile, even if there were no other new features at all. It’s just that good!