Will Query Store Work in a Read Only Database?

Desired-State-vs-Actual-State

For static databases, it’s quite useful to set SQL Server’s “read only” database property to true. When the database is read-only, it ensures that the last backup you took is still valid… as long as nothing bad happens to that backup file.

But can SQL Server 2016’s Query Store feature work with a read only database?

I tested this out on the new WideWorldImporters sample database from Microsoft. I made sure Query Store was working in the database, then set the database to read-only.

Upon refreshing my Query Store report…

Query Store Read Only

Life Lesson: Your Desired State May Not Be Your Actual State

If we take a look into sys.database_query_store_options, we get some insight into what’s going on here:

database_query_store_options

Our desired state is READ_WRITE, but our actual state is READ_ONLY. Reason: 1.

Digging into the documentation, the possible reasons our query store might go READ_ONLY include:

  • 1 – The database is read only
  • 2 – The database is single-user (I guess if you’re the only user, you’re responsible for monitoring your own query performance)
  • 4 – The database is in emergency mode (seems totally fair)
  • 8 – “log accept” mode. I’m not sure what this is. This is what you’ll see on readable secondaries (thanks to @rusanu for this info).
  • 65536 – Query Store outgrew the allotted size you configured for it
  • 131072 – Query Store reached the memory limit allowed for it in your version of SQL Database (this only applies if you’re using SQL Database)

If You Need Query Store, Your Database Must Be Read-Write

At least for now, Query Store can only record query performance in databases that are read-write. Once you go read-only you can review the performance of past queries, but you can’t track the performance of anyone who queried the database after the point it went read-only.

At least for now. Query Store is such an awesome feature that perhaps this will change in the future. (I don’t have any inside info, only optimism.)

Want a Workaround?

While the database must be read-write, Query Store uses only the Primary filegroup. You can potentially move your tables to a read-only filegroup and make it the default, but you’ll have to write custom code to keep anyone from using the Primary filegroup, or any other read-write filegroup. Read more on the SQLDoubleG blog.

Previous Post
Outside the Big SAN Box: Identifying Storage and SAN Latency in SQL Server (Dear SQL DBA)
Next Post
Fail Over, Fail Again, Fail Better – Preparing for Disaster Recovery (Dear SQL DBA)

Related Posts

5 Comments. Leave new

[…] Kendra Little wants to know if Query Store works on read-only databases: […]

Reply

[…] more information about this behaviour in a recent post by Kendra Little (b|t) where she shows that in order to get the new Query Store the database must be read-write, which I encourage you to read […]

Reply

[…] One of the reasons is how the Store behaves when the database goes READ_ONLY, which was earlier explained by Kendra Little (b|t) here […]

Reply

Thanks Kendra for the link back, have to say I got the idea while reading your post, so half the credit is yours

Reply

Leave a Reply

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

Menu