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:

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:

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:

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

2 thoughts on “Query Store and a READ_ONLY Database

Please let me know what you think about this article or any questions:

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