Infrastructure at your Service

Steven Naudet

dbi Event Recap: SQL Server Automatic Tuning and Query Store

Earlier this month, dbi services hosted a free multi-technology event. Details are available on our website.

I had the opportunity to present a session about the Query Store and the Automatic Tuning feature available on SQL Server and Azure SQL Database.
The first session in Lausanne was in French. A week later in Olten, I did it again in English.

Session attendees learned:

  • Query Store: Why, How, What?
  • How to identify and fix a Plan Regression
  • Automatic Tuning features

I received many great questions from attendees and colleagues about these topics. Here are the questions, along with my answers.

Query Store and Automatic Tuning Q&A

 

Q: Would you recommend enabling the Query Store by default on all databases?

A: Yes. As long as your SQL Server instance is kept up to date. Some important updates and fixes have been released with Cumulative Updates on all versions (2016, 2017, 2019). For example KB4340759, KB4091063. Please don’t start to use the Query Store in Production with an RTM version.

If you are on Azure SQL Database you are already having Query Store turned on as it’s enabled by default and cannot be disabled.

'QUERY_STORE=OFF' is not supported in this version of SQL Server.

 

Moreover, if you missed it, Microsoft just announced the next version of SQL Server which is SQL Server 2022. We already know that the Query Store will now be enabled by default for all databases. This indicates that this is a mature feature and it’s going to become a must-have. If you didn’t try the Query Store yet, I think you should.

Q: What is the overhead of Query Store performance-wise?

A: This question comes very often. In most cases, you don’t need to worry about Query Store overhead as it’s designed to be as less intrusive as possible.
However, if you have a high-volume workload of ad-hoc queries you might want to fine-tune your capture mode using the CUSTOM setting for the QUERY_CAPTURE_MODE option.

In case of emergency and if you doubt something’s wrong you can always turn the Query Store off and use the FORCED option which aborts all running Query Store background tasks and skips the synchronous flush when Query Store is turned off.

ALTER DATABASE <dbName> SET QUERY_STORE = OFF (FORCED);

 

Q: Can we limit the memory used by Query Store?

A: The space used to store data in the user database can be limited with the MAX_STORAGE_SIZE_MB setting. Regarding the memory used by Query Store, there is no such option.
Memory used by Query Store is part of the Buffer Pool and is fully managed by SQL Server. It should not cause any issue.

Improvements were made in this area as you can read in SQL Server 2019 CU8:

Query Store scalability improvement for adhoc workloads. Query Store now imposes internal limits to the amount of memory it can use and automatically changes the operation mode to READ-ONLY until enough memory has been returned to the Database Engine, preventing performance issues.

Query Store internal memory limits are not documented because they are subject to change.
If you want to look at what is actually consumed or monitor it, there are a few Memory Clerk you can look at with this kind of query:

--	Memory used by Query Store
SELECT SUM(pages_kb)/1024.
FROM sys.dm_os_memory_clerks
WHERE [type] IN (
	'CACHESTORE_QDSRUNTIMESTATS'
	,'MEMORYCLERK_QUERYDISKSTORE'
	,'MEMORYCLERK_QUERYDISKSTORE_HASHMAP'
	,'MEMORYCLERK_QUERYDISKSTORE_STATS'
	,'USERSTORE_QDSSTMT'
);

Remember you can clear the Query Store data at any time if you wish with the following command:

ALTER DATABASE <dbName> SET QUERY_STORE CLEAR ALL;

Q: Is Automatic Index Management available on-premise?

A: No, it is currently only available in Azure SQL Database.

Q: Do you recommend enabling Automatic Tuning?

A: Although the Automatic Plan Correction is enabled by default on Azure SQL Database, on-premise I would first recommend enabling it on a non-production environment, let it run for a while, study the recommendations and behaviors before making a well-informed decision in Production.

Q: What do the squares mean in Query Store reports?

A: The Square shape means that the query was canceled from the client-side. It happened in my demo after canceling a batch in SSMS.
You can also identify these queries using the “execution_type” columns from the DMV sys.query_store_runtime_stats.

 

That’s it for the Q&A. If you have any questions related to QS or Automatic Tuning do not hesitate to ask in the comments below and I’ll try to answer.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Steven Naudet
Steven Naudet

Consultant