Will Query Store Work in a Read Only Database?

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…

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:

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.