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; 1 2 3 4 5 6 7 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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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; 1 2 3 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

Share this: Twitter

Facebook

Reddit

LinkedIn

Tumblr

WhatsApp

Pocket

Email

