Why Did Query Store Switch to READ_ONLY?

How often do you evaluate your Query Store configuration? Have you ever had Query Store configured in READ_WRITE mode only to return later and find it in READ_ONLY mode instead? It may be simple enough to switch back to READ_WRITE and carry on with your day, but you would be wise to track down what happened to cause the switch to READ_ONLY.

There’s a View For That

To determine the reason for READ_ONLY, there is a system view called sys.database_query_store_options. In this view, you can check the readonly_reason column to find out why Query Store switched to READ_ONLY.

The column will show a bit map (seems a readonly_reason_desc would have helped here) so the reason may not be obvious. Summarizing what’s listed in Microsoft’s sys.database_query_store_options article, here are what the readonly_reason codes translate to:

• 65536 – Size limit set by the max_storage_size_mb was reached
• 131072 – Number of different statements reached the internal memory limit.
• 262144 – Size of in-memory items waiting to be persisted on disk reached the internal memory limit.
• 524288 – Database reached disk size limit

Triggering a READ_ONLY State

Let’s see if we can get Query Store to switch to READ_ONLY. The recommendation for Query Store is to start with max_storage_size_mb around 2048 MB and adjust from there as needed.

I’m only going to set it to 1 and run a few queries to get to the 1 MB limit.

Now when I query sys.database_query_store_options, actual_state is 1, actual_state_desc is READ_ONLY, and readonly_reason is 65536.

If we refer to what the readonly_reason codes translate to, it makes sense that 65536 means the max_storage_size_mb was reached.

Whatever reason you find, this gives you some direction on where you need to go so that you don’t find Query Store settings flipping against your will.

sp_BlitzQueryStore to sp_QuickieStore

On the topic of Query Store, I’ll point out that if you use sp_BlitzQueryStore from Brent Ozar’s First Responder Kit, now is a good time to start using Erik Darling’s sp_QuickieStore instead because sp_BlitzQueryStore is being deprecated. From Brent’s latest post about First Responder Kit updates:

“sp_BlitzQueryStore was originally written by Erik Darling when he worked here. He’s moved on to start his own excellent company, plus his own sp_QuickieStore. You should be using that instead.”

Thanks for reading!