Edit: November 12, 2020 – Updated information about Query Store performance overhead can be found in this post.
“What is the performance overhead of enabling Query Store?”
I get asked this question almost every time I present on a topic related to Query Store. What people are really asking is “Does enabling Query Store affect the performance of my queries?” Where “my queries” are user queries, queries from the application, etc.
The short answer:
- The majority of workloads won’t see an impact on system performance
- Will there be an increase in resource use (CPU, memory)? Yes.
- Is there a “magic number” to use to figure out Query Store performance and the increase in resource use? No, it will depend on the type of workload. Keep reading.
- An impact on system performance can be seen with ad-hoc workloads (think Entity Framework, NHibernate), but I still think it’s worth enabling. With an ad-hoc workload there are additional factors to consider when using Query Store.
- You should be running the latest version CU for SQL Server 2017 and latest CU for SQL Server 2016 SP2 to get all performance-related improvements Microsoft has implemented specific to Query Store
The long answer…
One reason the SQL Server 2016 release was such a solid release was that it was data driven. “Data Driven” was frequently used in Microsoft marketing materials for 2016, but it wasn’t hype; it was true. At the time of the SQL Server release, Azure SQL Database had been in existence for over two years and Microsoft had been capturing telemetry and using that data to understand how features were being used, as well as improve existing features.
One of the features that benefited most from the insight provided by the telemetry data was Query Store, which was originally released in private preview for Azure SQL Database in early 2015. As Query Store was implemented for more databases, the information captured was used to enhance its functionality and improve its performance. Query Store was made publicly available in late 2015, and included in the SQL Server 2016 release. The telemetry data was invaluable to Microsoft’s developers as they prepared Query Store for release, but the variety in size and workload that exist in on-premises solutions was not accurately represented. Much of this was due to limitations in Azure tiers at the time and the limited number (comparatively) of companies that had embraced using a cloud solution.
Thus, while the initial internal thresholds for Query Store were determined based upon Azure SQL Database solutions and appropriate for most on-prem systems, they were not fully suited to every variation of an on-prem solution. This is not atypical – it’s extremely difficult to develop software that accommodates every single workload in the world both in the cloud and on-prem.
This history is relevant when people ask about solution performance and Query Store.
First, understand that there are differences in how Query Store works in Azure SQL Database compared to on-prem. A good example is the amount of space that you can allocate to Query Store within the user database (MAX_STORAGE_SIZE_MB). In Azure SQL Database the maximum value one can set for MAX_STORAGE_SIZE_MB is 10GB. There is no a limit for SQL Server 2016 or 2017. As a result of this limitation for Azure SQL DB, the amount of data that Query Store has to manage can be significantly less than what we see for an on-prem solution. There are many production environments with a Query Store that is 10GB or less in size, but I know of Query Stores that are 200-250GB in size on disk, which typically indicates an anti-pattern with the workload.
Separate from storing the Query Store in the user database, data is also held in different memory buffers (e.g. query hash map, runtime statistics cache store). Data is inserted into these memory buffers for new queries, updated for previously-executed queries, and while data is flushed to disk regularly, it is expected that data continuously resides in these buffers. The data for the query hash map is consistent, but the volume of data in the runtime statistics cache store fluctuates depending on the workload.
There are multiple ways to characterize a workload, but in the case of Query Store, we’re most interested in the number of unique queries generated. We tend to characterize workloads with a high number of unique queries as ad-hoc – those that use Entity Framework or NHibernate, for example. But there are other variations, such as multi-versioned tables, which also create a significant number of unique queries. To be clear, the following are unique queries:
d.Name FROM dbo.Employees e JOIN dbo.Department d ON e.department_id = d.department_id WHERE e.LastName = ‘Harbaugh’;
d.Name FROM dbo.Employees e JOIN dbo.Department d
ON e.department_id = d.department_id
WHERE e.LastName = ‘Winovich’;
d.Name FROM dbo.Employees e JOIN dbo.Department d ON e.department_id = d.department_id WHERE e.lastname = ‘Carr’;
Just like the plan cache, Query Store identifies each of the above queries as unique (even though they all have the same query_hash) based on the text, and assigns each its own query_text_id in Query Store. This query_text_id, combined with context_settings_id, object_id, batch_sql_handle, and query_parameterization_type create a unique hash for each query which Query Store uses internally, and is stored in buffers in memory, along with the runtime statistics for each unique hash. The more unique query texts in a workload, the more overhead there may be to manage the data.
Understand that if you have an ad hoc workload, you already have a system that is prone to performance issues due to high compiles, plan cache bloat, and variability in query performance across queries that are textually the same in terms of query_hash, but have different literal values (as shown above). For an ad-hoc workload that is also high volume (high number of batch requests/sec), when you enable Query Store it can appear that a performance problem has been introduced. It is tempting to look at any decrease in performance as a problem with Query Store. However, it’s a function of the type of the workload and simply the cost of doing business for said workload. If you want to capture Query Store data about an ad-hoc workload (to then identify query patterns and address them) then you’ll have to expect and plan for the overhead associated with it.
You can control, to a small degree, the number of queries captured using the QUERY_CAPTURE_MODE setting. The default value of ALL means that every single executed will be captured. The value of AUTO means that only queries that exceed a threshold (set internally by Microsoft) will be captured. As noted in my Query Store Settings post, AUTO is the recommendation for a production environment, particularly one that is ad-hoc.
The SQL Server team made performance-related improvements in Query Store in the SQL Server 2017 release, and these were back-ported to SQL Server 2016 SP2. There have been a few additional fixes in SQL Server 2017, such as this one, in CU11. I know of a couple people who have run into this issue, so if you’re on SQL Server 2017 and using Query Store, I definitely recommend applying the latest CU.
Now we can answer, “Can enabling Query Store make some of your queries run slower?” It depends on your workload, your version of SQL Server, and the settings you have enabled. For those folks with a mostly procedure-type workload, I haven’t seen many issues. For those with ad-hoc, high volume workloads, you are now aware of the potential overhead and you can plan accordingly. If you’re on the fence about it, enable it during a low-volume time and monitor the system. If you feel there’s a problem, turn it off. But the data gathered on any system can be used to help make that system better, even if you have to do it incrementally. Whether your workload is procedure-based, ad-hoc, or a mix, Query Store is an invaluable resource that can be used to capture query metrics, find queries that perform poorly or execute frequently, and force plans to stabilize query performance.