Query Store and a READ_ONLY Database

What happens in Query Store when the database itself is READ_ONLY?

Yeah, I don’t know. Let’s find out.

READ_ONLY

The only way to find out how this works is to test it. So, let’s write some code:

CREATE DATABASE testquerystore;
GO
ALTER DATABASE testquerystore 
SET READ_ONLY;
GO
ALTER DATABASE testquerystore
SET QUERY_STORE = ON;

Executing that resulted in a small glitch in the Matrix:

8:00:54 AM
Started executing query at Line 1

Commands completed successfully.
8:00:54 AM
Started executing query at Line 2

Commands completed successfully.
8:00:54 AM
Started executing query at Line 5

Msg 5004, Level 16, State 6, Line 5
To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.

Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.

Total execution time: 00:00:01.448

Well that’s not going to work. Here’s more code:

ALTER DATABASE testquerystore
SET READ_WRITE;
GO
ALTER DATABASE testquerystore
SET QUERY_STORE = ON;
GO
ALTER DATABASE testquerystore 
SET READ_ONLY;
GO
USE testquerystore
GO
SELECT qsq.query_id 
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE 'SELECT qsq.query_id%';
GO

I’ll run the last query there twice, once to get it loaded into Query Store and once to see the results. Unfortunately, this is what I got:

So what’s the story? More code:

SELECT dqso.desired_state_desc,
dqso.actual_state_desc 
FROM sys.database_query_store_options AS dqso;

Which results in:

Conclusion

When I was asked this question, I had a very good guess what the answer was. You can’t have a database that is READ_ONLY and collect Query Store data. It just doesn’t work like that. However, I didn’t know it for certain. So, a little testing confirmed my guess. A database marked READ_ONLY is in fact read only, even for the Query Store.

Now, any data collected to the Query Store while the database was READ_WRITE will still be there. Just as changing a database to READ_ONLY doesn’t hurt the existing user data, it doesn’t hurt the system data either.

If you want to collect Query Store information then make sure your database is set to READ_WRITE.

Want to learn all about what Query Store can do, not just what it can’t do? Want to also learn about a lot of other SQL Server tooling focused around query tuning? You should come to one of my all day seminars:

Training Day, SQLBits, February 28, 2019, Manchester, UK
SQL Day, May 13, 2019, Wroclaw, Poland
SQLSaturday Columbus Precon, June 7 2019, Columbus OH