Why Query Store Actual Operation Mode is Not Same as Requested? – Interview Question of the Week #248

Question: Why Query Store Actual Operation Mode is Not Same as Requested?

Answer: Recently, one of the largest bank institutes hired me to help them with their SQL Server Performance. The biggest issue which they were facing was query regression. While working with the Comprehensive Database Performance Health Check, I was able to help them tune their queries. As this was an extreme case of the Query Regression (poor performing queries which were once working great), we had to use a query store. When we were done with the consultation, I also taught them how to use the query store.

Everything was working fine when we were done, however, three days later their DBA reached out to me with a very strange problem. The DBA sent me an image of one of the databases. The issue was that the database had different states of the query stored in the configuration.

Query Store Operation Mode

In their case, the Query Store Actual Operation Mode was Read Only whereas their Query Store Requested Operation Mode was set to Read Write. They were not able to understand why the database was not accepting their request and the actual mode was not changed for the query store. This was indeed very strange for them and they had restarted the server and did a few other standard tricks. Finally, they reached out to me asking the question with the following image.

As I have worked extensively with SQL Server Performance Tuning issues, fortunately, I knew the answer of this one. This happens when you accidentally mark your database as a Read-only, query store’s actual mode also resets to Read Only.

Please remember that the query store keeps all its data into the database itself so when you make your database as read-only, your query store now no longer can write to your database hence it has to change its actual state to read-only.

Read-Only Database

-- Script to make database read only 
USE [master]
GO
ALTER DATABASE [TestDb] SET READ_ONLY WITH NO_WAIT
GO

Read Write Database

-- Script to make database read write
USE [master]
GO
ALTER DATABASE [TestDb] SET READ_WRITE WITH NO_WAIT
GO

Once my client moved their database from read-only settings from False to True, the Query Store Actual Operation Mode also automatically get changed to Read Write.

I really wished that my client had asked me this question instead of spending an hour of time looking for the solution and restarting the database. I personally do not like to restart my database services as it affects my database history negatively. More about this, I will blog in the future.

If you have any SQL Server Performance Tuning issue, you should reach out to me immediately. Trust me, I have been working on this area so long, I might just know the answer and help you immediately. Write a quick email and you will get a response immediately as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)