Search code examples
sqlsql-server-2016sql-query-store

Query Store does not record any data

I have enabled the Query Store for two of my databases (acceptance and production), which are both running on the same instance of SQL Server 2016 Standard Edition. The Query Store records query history on the acceptance database, but on the production database it does not record any data.

The two databases are configured identically, with the exception of mirroring that is only enabled for the production database. The mirroring mode used is "High safety with automatic failover (synchronous)".

Solution

  • 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.

    Known Limitations of Query Store

    1. No Information on Who ran / Which Program ran since Query Store provides no data related to Application Name.
    2. Query store cannot be enabled for system databases like master or tempdb
    3. Lack of Control - Multiple DBAs could change settings
    4. Data is stored in Primary filegroup
    5. Data captured at batch level is not available.
    6. Query Wait Stats Store available starting from SQL Server 2017
    7. Arbitrary values are not allowed for INTERVAL_LENGTH_MINUTES in Statistics Collection Interval. (1, 5, 10, 15, 30, 60, or 1440 minutes)