How to fix Query Store in READ_ONLY mode due to read_only reason 262144

Question

How to fix Query Store in READ_ONLY mode due to read_only reason 262144

Pavel Spitz 30

We are running

Microsoft SQL Server 2022 Enterprise edition version 16.0.4115.5 (X64)

SQL Server has 254 Cores, 7.5 TB RAM dedicated for SQL Server
SQL Server is running in HA cluster -1 primary and 3 secondary replicas (1 synchronous, 2 asynchronous commit)
Number of databases on the SQL Server are 166.

Query store is enabled in all our databases and configured with default values via Microsoft recommendations (meaning documentation) in primary replica only. Exception is MAX_STORAGE_SIZE_MB which is set to approprite value for every database.

Query store on many databases has started automatically switching to READ_ONLY mode and back to READ_WRITE after we installed SQL Server 2022. Switching to READ_ONLY mode is due to readonly_reason= 262144 (i.e. query store internal memory limit was reached).
We tried to find some information about the query store internal memory limit, but there isn't any information about value of the limit value and how this can be increased. We found only information that this value of memory limit can be automatically set by SQL Server engine and changed in according to server size of memory.

The situation on many databases is rather problematic for us, because switching to READ_ONLY mode is depend on the database load and amount of queries. Some of the most heavily loaded databases are switched to READ_ONLY mode practically forever. With this new feature of Query Store, we practically lost information about the queries running there mainly on our most important databases.

Are there any possibilities how to prevent query store automatically switched to READ_ONLY mode due to the reason 262144?

Anonymous

2024-08-20T01:58:05.1366667+00:00

Hi @Pavel Spitz,

Could you please tell us if there is any new progress on this issue?

We are still very concerned about your issue!

In addition, I don't want to nag you, but maybe the answers below are helpful. May I humbly suggest that please accept Answer if they helped.

By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

The links and methods we provide will also help them resolve their issue or follow up this issue with us together!

Thanks for your understanding. Your encouragement and recognition are the driving force for our progress!

Best regards,

Lucy Chen
Pavel Spitz 30 Reputation points

2024-08-20T08:55:27.4+00:00

Hi ,There is not any progress of solution of this issue. Now we are waiting for server restart or failover to another machine to exclude potentially HW problem. If this problem will still take we will contact Microsoft support. The restart of this server can't be provided at any time because this server is our main production server and we must wait for time window that is given for this operation.
I reckon I'll let you know if it helped or not.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-20T20:57:05.45+00:00

I find it difficult to believe that this would be due to a HW issue. But, yeah, when you open a support case, that may be what the support engineer starts asking about...
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-21T19:37:57.5066667+00:00
I spoke with a person who pointed out a few things.

First, what does your workload look like? Do you have a lot of unparameterised queries? Such workload does not play well with Query Store. (Well, such a workload does not play well with anything.)

My friend also pointed out there are a more memory clerks that are of interest. Have a look at these:

SELECT * FROM sys.dm_os_memory_clerks WHERE type LIKE '%QUERYDISK%'

I reviewed the Query Store configuration you posted previously. Looks pretty normal to me, but one thing: You have set the interval length to 15 minutes, as opposed the default of 60. It is not entirely obvious how that would affect the memory issue, but possibly you should revert to 60 and see if it makes a difference.
Matúš Hološ 0 Reputation points

2024-08-21T19:57:14.1533333+00:00

Hello Erland Sommarskog. Thank you for your contributions to the discussion. I have the opportunity to analyze this issue that Pavel Spitz described. It is not easy for us to change the interval, as other monitoring systems depend on it. This setup has worked for years. Since the last server restart, the issue has been occurring daily with high intensity. We plan to restart the server in a few days. The query you provided returns the following results:
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-21T20:04:49.8233333+00:00
That is still not a lot. Hm...

What about paramerised queries? Do you have any idea about that?

Here is a query to investigate which you could run in databases which have experienced this problem:

SELECT qsq.query_id, qsqt.query_sql_text FROM sys.query_store_query AS qsq INNER JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id= qsqt.query_text_id WHERE query_parameterization_type=0;
Matúš Hološ 0 Reputation points

2024-08-21T20:17:14.59+00:00

This is what the data looks like.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-21T20:23:03.1633333+00:00

Ouch! That does not look good, lots of unparameterised queries it seems like.

You also have a couple of forced, which suggests you have set this database to forced parameterisation to alleviate the poor client design (using unparameterised queries is nothing but bad practice). But it does not seem to kick very often. There are situations where forced parameterisation does not set in, but that many queries?

These 166 databases, are they just 166 random databases assembled together? Or are they all the same schema for a multi-tenant application?
Pavel Spitz 30 Reputation points

2024-08-26T11:42:26.32+00:00
These 166 databases are not random database of a half. We use same database schema for various countries. The others are different developped for various cooperative services or microservices.

The design of services, mainly using frameworks and other tools (EntityFramework, LINQ, ...), is in developpers hand. We have given them recommnedations not to use unparametrized ad-hoc queries very often, because this is not very good for SQL Server engine and for their's services too. But we know, that the situation in this matter is not very good (the result can be seen below).

We use Kubernetes too. This tool accelerated our services via parallelism - this is probably the reason of relatively high number of unparametrized queries too.

There is a little statistics of Ahdhoc queries on the server:

SELECT plans_all = count(*) ,plans_adhoc = SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) ,plans_adhoc_percentage = CONVERT(INT, SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) * 1.00 / Count(*) * 100) FROM sys.dm_exec_cached_plans AS p
Pavel Spitz 30 Reputation points

2024-08-26T12:05:10.72+00:00
These 166 databases are not random database of a half. We use same database schema for various countries. The others are different developped for various cooperative services or microservices.

The design of services, mainly using frameworks and other tools (EntityFramework, LINQ, ...), is in developpers hand. We have given them recommnedations not to use unparametrized ad-hoc queries very often, because this is not very good for SQL Server engine and for their's services too. But we know, that the situation in this matter is not very good (the result can be seen above).

We usage Kubernetes too. This tool accelerated our services via parallelism - this is probably the reason of relatively high number of unparametrized queries too.

There is a statistics of Adhoc queries on the server:

SELECT plans_all = count(*) ,plans_adhoc = SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) ,plans_adhoc_percentage = CONVERT(INT, SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) * 1.00 / Count(*) * 100) FROM sys.dm_exec_cached_plans AS p
Pavel Spitz 30 Reputation points

2024-08-26T13:07:54.2266667+00:00

Hi, these 166 databases on our server are not random databases only of a half. We use same database schema for various countries. The others are different developped for various cooperative services or microservices. The design of services, mainly using frameworks and other tools (EntityFramework, LINQ, ...), is in developpers hand. We have given them recommnedations not to use unparametrized ad-hoc queries very often, because this is not very good for SQL Server engine and for their's services too. But we know, that the situation in this matter is not very good (the result can be seen above). We usage Kubernetes too. This tool accelerated our services via parallelism - this is probably the reason of relatively high number of unparametrized queries too.

4 answers

Your answer

Anonymous

2024-08-20T01:58:05.1366667+00:00

Hi @Pavel Spitz,

Could you please tell us if there is any new progress on this issue?

We are still very concerned about your issue!

In addition, I don't want to nag you, but maybe the answers below are helpful. May I humbly suggest that please accept Answer if they helped.

By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

The links and methods we provide will also help them resolve their issue or follow up this issue with us together!

Thanks for your understanding. Your encouragement and recognition are the driving force for our progress!

Best regards,

Lucy Chen
Pavel Spitz 30 Reputation points

2024-08-20T08:55:27.4+00:00

Hi ,There is not any progress of solution of this issue. Now we are waiting for server restart or failover to another machine to exclude potentially HW problem. If this problem will still take we will contact Microsoft support. The restart of this server can't be provided at any time because this server is our main production server and we must wait for time window that is given for this operation.
I reckon I'll let you know if it helped or not.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-20T20:57:05.45+00:00

I find it difficult to believe that this would be due to a HW issue. But, yeah, when you open a support case, that may be what the support engineer starts asking about...
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-21T19:37:57.5066667+00:00

I spoke with a person who pointed out a few things.

First, what does your workload look like? Do you have a lot of unparameterised queries? Such workload does not play well with Query Store. (Well, such a workload does not play well with anything.)

My friend also pointed out there are a more memory clerks that are of interest. Have a look at these:

SELECT * FROM sys.dm_os_memory_clerks WHERE type LIKE '%QUERYDISK%'

I reviewed the Query Store configuration you posted previously. Looks pretty normal to me, but one thing: You have set the interval length to 15 minutes, as opposed the default of 60. It is not entirely obvious how that would affect the memory issue, but possibly you should revert to 60 and see if it makes a difference.
Matúš Hološ 0 Reputation points

2024-08-21T19:57:14.1533333+00:00

Hello Erland Sommarskog. Thank you for your contributions to the discussion. I have the opportunity to analyze this issue that Pavel Spitz described. It is not easy for us to change the interval, as other monitoring systems depend on it. This setup has worked for years. Since the last server restart, the issue has been occurring daily with high intensity. We plan to restart the server in a few days. The query you provided returns the following results:
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-21T20:04:49.8233333+00:00

That is still not a lot. Hm...

What about paramerised queries? Do you have any idea about that?

Here is a query to investigate which you could run in databases which have experienced this problem:

SELECT qsq.query_id, qsqt.query_sql_text FROM sys.query_store_query AS qsq INNER JOIN sys.query_store_query_text AS qsqt ON qsq.query_text_id= qsqt.query_text_id WHERE query_parameterization_type=0;
Matúš Hološ 0 Reputation points

2024-08-21T20:17:14.59+00:00

This is what the data looks like.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-21T20:23:03.1633333+00:00

Ouch! That does not look good, lots of unparameterised queries it seems like.

You also have a couple of forced, which suggests you have set this database to forced parameterisation to alleviate the poor client design (using unparameterised queries is nothing but bad practice). But it does not seem to kick very often. There are situations where forced parameterisation does not set in, but that many queries?

These 166 databases, are they just 166 random databases assembled together? Or are they all the same schema for a multi-tenant application?
Pavel Spitz 30 Reputation points

2024-08-26T11:42:26.32+00:00

These 166 databases are not random database of a half. We use same database schema for various countries. The others are different developped for various cooperative services or microservices.

The design of services, mainly using frameworks and other tools (EntityFramework, LINQ, ...), is in developpers hand. We have given them recommnedations not to use unparametrized ad-hoc queries very often, because this is not very good for SQL Server engine and for their's services too. But we know, that the situation in this matter is not very good (the result can be seen below).

We use Kubernetes too. This tool accelerated our services via parallelism - this is probably the reason of relatively high number of unparametrized queries too.

There is a little statistics of Ahdhoc queries on the server:

SELECT plans_all = count(*) ,plans_adhoc = SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) ,plans_adhoc_percentage = CONVERT(INT, SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) * 1.00 / Count(*) * 100) FROM sys.dm_exec_cached_plans AS p
Pavel Spitz 30 Reputation points

2024-08-26T12:05:10.72+00:00

These 166 databases are not random database of a half. We use same database schema for various countries. The others are different developped for various cooperative services or microservices.

The design of services, mainly using frameworks and other tools (EntityFramework, LINQ, ...), is in developpers hand. We have given them recommnedations not to use unparametrized ad-hoc queries very often, because this is not very good for SQL Server engine and for their's services too. But we know, that the situation in this matter is not very good (the result can be seen above).

We usage Kubernetes too. This tool accelerated our services via parallelism - this is probably the reason of relatively high number of unparametrized queries too.

There is a statistics of Adhoc queries on the server:

SELECT plans_all = count(*) ,plans_adhoc = SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) ,plans_adhoc_percentage = CONVERT(INT, SUM(CASE p.objtype WHEN 'Adhoc' THEN 1 ELSE 0 END) * 1.00 / Count(*) * 100) FROM sys.dm_exec_cached_plans AS p
Pavel Spitz 30 Reputation points

2024-08-26T13:07:54.2266667+00:00

Hi, these 166 databases on our server are not random databases only of a half. We use same database schema for various countries. The others are different developped for various cooperative services or microservices. The design of services, mainly using frameworks and other tools (EntityFramework, LINQ, ...), is in developpers hand. We have given them recommnedations not to use unparametrized ad-hoc queries very often, because this is not very good for SQL Server engine and for their's services too. But we know, that the situation in this matter is not very good (the result can be seen above). We usage Kubernetes too. This tool accelerated our services via parallelism - this is probably the reason of relatively high number of unparametrized queries too.

Answer 1

Pavel Spitz 30

We have found out the reason why QueryStore is almost silently switched to ReadOnly mode. The cause was a past attempt to eliminate the high spinlock of SOS_CACHESTORE by enabling TF 174. We found it in our logs that the QueryStore problem is temporally correlated with turning on this TF 174. We have now turned it off and performed a Failover and the problem with QueryStore automatically switching to ReadOnly mode has disappeared.

I apologize you for the longer reaction, but turning off TF and performing Failover was a time-consuming operation. We must always plan the time of the Failover execution with sufficient time in advance.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-09-05T20:15:34.6166667+00:00

No need for apologies. It is your problem, not ours, so it is up to you to when to work and test solutions.

Thanks a lot for taking the time to share the solution!

Answer 2

Anonymous

Hi @Pavel Spitz,

Thank you for your reaching out and welcome to Microsoft Q&A!

Are there any possibilities how to prevent query store automatically switched to READ_ONLY mode due to the reason 262144?

Once the query store is enabled, you really need to monitor things to make sure the settings work for your workload. There are some guidelines in the following documentation: Best Practice with the Query Store. (There is no link to the documentation you mentioned. Maybe it is this article I provide here.)

You could also refer to this official documentation to improve the management of Query Store.

https://callihandata.com/2024/06/03/why-did-query-store-switch-to-read_only/

In addition, it provides steps about Query Store Data Cleanup manually in detail, hope it can help you resolve the issue.

Feel free to share your issues here if you have any concerns!

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Pavel Spitz 30 Reputation points

2024-08-16T15:37:05.3033333+00:00
Thank you very much for your attention, Lucy Chen.

Our setting of Query store for our databases is:

ALTER DATABASE [...] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 15, MAX_STORAGE_SIZE_MB = 65536, MAX_PLANS_PER_QUERY = 200, DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = AUTO, WAIT_STATS_CAPTURE_MODE = ON, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 1) );

This setting was provided in according to Microsoft documentation and was function until we upgraded to SQL Server 2022.

The documentations Best Practice with the Query Store and Best practices for managing the Query Store I read and check all recommendations against our settings. I must say thath we have turned on all recommended switches except trace flags. However, our database suffers from a high load of adhoc (non-parametrized) query. I think that this is the problem switching Query Store to READ_ONLY mode.

I tried to change settings of Query Store to CUSTOM settings and tried set this parameters:

ALTER DATABASE [...] SET QUERY_STORE (OPERATION_MODE = READ_ONLY); ALTER DATABASE [...] SET QUERY_STORE CLEAR ALL ; ALTER DATABASE [...] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 65536, MAX_PLANS_PER_QUERY = 20, DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = ( STALE_CAPTURE_POLICY_THRESHOLD = 1 HOURS, EXECUTION_COUNT = 1000, TOTAL_COMPILE_CPU_TIME_MS = 1000, TOTAL_EXECUTION_CPU_TIME_MS = 120000 ) ); ALTER DATABASE [..] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

However, these new settings did not have the desired effect - Query Store was also switched to READ_ONLY mode automatically without any back to READ_WRITE mode. I think that this a new feature introduced with this important notice in Best Practice with the Query Store caused our botleneck:

This is the reason why our Query Store is automatically switched between READ_WRITE and READ_ONLY mode due to reason 262144 which means:

'Size of in-memory items waiting to be persisted on disk reached the internal memory limit. Query Store is in read-only mode temporarily until the in-memory items are persisted on disk.'

This is from documentation of sys.database_query_store_options.

We tried also run stored procedure to flush data from memory to disk (sp_query_store_flush_db):

declare @retval int; exec sp_query_store_flush_db; select @retval;

But this procedure returned NULL on output. This a little weird because its returned number should be 0 or 1.After execution this procedure was behaviour of the Query store the same.Finally, I must mention that this is not behaviour the only database on the server, but several. Almost all of them return from the READ_ONLY mode to READ_WRITE, but the only one, unfortunately the largest one, which has about 10 TB, practically does not return to the READ_WRITE mode at all or only a little.
Pavel Spitz 30 Reputation points

2024-08-16T15:39:56.23+00:00
Thank you very much for your attention, Lucy Chen.

Our setting of Query store for our databases is:

ALTER DATABASE [...] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 15, MAX_STORAGE_SIZE_MB = 65536, MAX_PLANS_PER_QUERY = 200, DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = AUTO, WAIT_STATS_CAPTURE_MODE = ON, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 1) );

This setting was provided in according to Microsoft documentation and was function until we upgraded to SQL Server 2022.

The documentations Best Practice with the Query Store and Best practices for managing the Query Store I read and check all recommendations against our settings. I must say thath we have turned on all recommended switches except trace flags. However, our database suffers from a high load of adhoc (non-parametrized) query. I think that this is the problem switching Query Store to READ_ONLY mode.

I tried to change settings of Query Store to CUSTOM settings and tried set this parameters:

ALTER DATABASE [...] SET QUERY_STORE (OPERATION_MODE = READ_ONLY); ALTER DATABASE [...] SET QUERY_STORE CLEAR ALL ; ALTER DATABASE [...] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 65536, MAX_PLANS_PER_QUERY = 20, DATA_FLUSH_INTERVAL_SECONDS = 900, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = ( STALE_CAPTURE_POLICY_THRESHOLD = 1 HOURS, EXECUTION_COUNT = 1000, TOTAL_COMPILE_CPU_TIME_MS = 1000, TOTAL_EXECUTION_CPU_TIME_MS = 120000 ) ); ALTER DATABASE [..] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

However, these new settings did not have the desired effect - Query Store was also switched to READ_ONLY mode automatically without any back to READ_WRITE mode. I think that this a new feature introduced with this important notice in Best Practice with the Query Store caused our botleneck:

This is the reason why our Query Store is automatically switched between READ_WRITE and READ_ONLY mode due to reason 262144 which means:

'Size of in-memory items waiting to be persisted on disk reached the internal memory limit. Query Store is in read-only mode temporarily until the in-memory items are persisted on disk.'

This is from documentation of sys.database_query_store_options.

We tried also run stored procedure to flush data from memory to disk (sp_query_store_flush_db):

declare @retval int; exec sp_query_store_flush_db; select @retval;

But this procedure returned NULL on output. This a little weird because its returned number could be 0 or 1. After execution this procedure was behaviour of the Query store the same.

Finally, I must mention that this is not behaviour the only database on the server, but several. Almost all of them return from the READ_ONLY mode to READ_WRITE, but the only one, unfortunately the largest one, which has about 10 TB, practically does not return to the READ_WRITE mode at all or only a little.

Answer 3

Erland Sommarskog 121.4K MVP Volunteer Moderator

I would recommend that you open a support case. Given the beefy server you have, I am inclined to think that you have a beefy support contract as well.

There are several reasons I make this suggestion. One is that this sounds way more advanced than we have knowledge for here in the forums. A qualified escalation engineer may be able to give you good advice.

It could also be that the answer is "tough luck", but this is the situation you play the card "this is not acceptable for us", and if you play it well enough, Microsoft will have to think of a way to improve the situation.

Of course, that depends on exactly how much memory of all those 7.5 RAM that Query Store is consuming. A query like

SELECT * FROM sys.dm_os_memory_clerks  WHERE type LIKE '%QDS%'

may reveal something. My gut feeling that the total will be way below those 7.5 TB. But I've been wrong before.

Pavel Spitz 30 Reputation points

2024-08-18T10:32:01.8633333+00:00

Thank you very mutch for your answer, Erland Sommarskog.

I tried your query and here is the result - providing sum of pages_kb per type and total sum:

type_of memory_clerk pages_kb

CACHESTORE_QDSCONTEXTSETTINGS 2672

CACHESTORE_QDSRUNTIMESTATS 43416

USERSTORE_QDSSTMT 2896

Total 48984

Considering the total memory of DB server, it's not very much. I would have expected it to be more.

I'm inclined to your opinion to contact Microsoft support with this problem.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-08-18T10:34:55.2+00:00

Yeah, I agree. Certainly not frightening. And moreover, it is good ammo when you talk with Microsoft.

Answer 4

Pavel Spitz 30

I am very sorry for my previous post where I wrote that we had found reason of our problem with query store automatic switching to READ_ONLY mode by turnning off TF174. Our database's query stores on the server have perfectly run without switching about 24 hours, but now this problem has been completely back.

Now we will contact Microsoft support.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-09-06T13:47:50.45+00:00

Too bad! Please keep us posted about the outcome!

type_of memory_clerk	pages_kb
CACHESTORE_QDSCONTEXTSETTINGS	2672
CACHESTORE_QDSRUNTIMESTATS	43416
USERSTORE_QDSSTMT	2896
Total	48984

Share via

How to fix Query Store in READ_ONLY mode due to read_only reason 262144

4 answers

Your answer