I have a primary db and a secondary geo-replicated db. On the primary, the server atuomatic tuning is turned on.
On the replica when I try to do the same I encounter the following issues.
The database is inheriting settings from the server, but the server is in the unspecified state. Please specify the automatic tuning state on the server.
And
Automated recommendation management is disabled because Query Store has reached its capacity limit and is not collecting new data. Learn more about the retention policies to maintain Query Store so new data can be collected.
However, on the server, tuning options are on so I don't understand that "unspecified state". Moreover, why I look at the Query Store set up in both databases properties in SSMS they are exactly the same with 9MB of space available out of 10MB.
Note: both databases are setup on the 5 DTUs basic pricing plan.
UPDATE
While the primary db Query store Operation Mode is Read Write, the replica is Read Only. It seems I cannot change it (I couldn't from the properties dialog of the db in SSMS).
Fair enough but how can the same query be 10 times faster on primary than on replica. Aren't optimizations copied accross?
UPDATE 2
Actually Query Store are viewable on SSMS and I can see that they are identical in both db. I think the difference in response times that I observe is not related.
UPDATE 3
I marked @vCillusion's post as the answer as he/she deserves credits. However, it's too detailed with regards to the actual issue.
My replica is read-only and as such cannot be auto-tuned as this would require writing in the query store. Azure not being able to collect any data into the read only query store led to a misleading (and wrong) error message about the query store reaching its capacity.
We get this message only when the Query Store is in read-only mode. Double check your query store configuration. According to MSDN, you might need to consider below:
To recover Query Store try explicitly setting the read-write mode and recheck actual state.
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
max_storage_size_mb, readonly_reason, interval_length_minutes,
stale_query_threshold_days, size_based_cleanup_mode_desc,
query_capture_mode_desc
FROM sys.database_query_store_options;
If the problem persists, it indicates corruption of the Query Store data and continues on the disk. We can recover Query Store by executing sp_query_store_consistency_check stored procedure within the affected database.
If that did not help, you could try to clear Query Store before requesting read-write mode.
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
max_storage_size_mb, readonly_reason, interval_length_minutes,
stale_query_threshold_days, size_based_cleanup_mode_desc,
query_capture_mode_desc
FROM sys.database_query_store_options;
If you checked it and it's in read-write mode, then we might be dealing with some bug here. Please provide feedback to Microsoft on this.
Additional points of limitation in query store:
Also, note Query Store feature is introduced to monitor performance and is still evolving. There are certain known limitations around it.
As of now, it does not work on Read-Only databases (Including read-only AG replicas). Since readable secondary Replicas are read-only, the query store on those secondary replicas is also read-only. This means runtime statistics for queries executed on those replicas are not recorded into the query store.