Product has been added to your cart.

Query Store Disabled

Enable Query Store for Performance Tracking

SQL Server 2016 and Azure SQL Database added an amazing new feature – the Query Store. It’s like a flight data recorder for SQL Server, tracking which queries use the most resources and how their query plans change over time. Starting with SQL Server 2017, SQL Server can even automatically switch back to using a prior execution plan if there’s a performance problem with a new plan.

The time to enable Query Store isn’t when you’re having BAD performance. At that point, it’s too late – you won’t know what the good query plans used to look like. The time to enable it is NOW, when you’re having decent performance. This way you can look back over time to compare query plans.

Query Store does come with a couple of big gotchas:

  • You need to stay current on patches. In the first few years of release (2016-2019), Microsoft fixed a lot of bugs with Query Store, like bugs that would cause a severe slowdown or an outright outage.
  • You need to configure it correctly. The defaults on 2016-2017 are absolutely terrible: they try to capture all queries, which leads to huge performance overhead.

But as long as you follow those two things, it’s got a big payoff for performance tuning. To get started learning about it, check these out:

If you read this page and decide that Query Store isn’t for you, that’s okay – you can always make that conscious decision for now, and ignore/mute that recommendation. Query Store only makes sense if you’re going to invest the time in learning how to use it for performance tuning.