Query store is set to read only

  • TRACEY-320982

    SSChampion

    Points: 13480

    I have the database and it says that the querystore is set to read only.

    Do i just run the following commands?

    --Clean it up
    ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;

    --Set it back to read write
    ALTER DATABASE [QueryStoreDB]
    SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
    GO

    The threshold i have are 367 days- reduce it to 90
    ALTER DATABASE [QueryStoreDB]
    SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));



  • Sue_H

    SSC Guru

    Points: 89970

    Yes. The last two options can be set in a single alter database. But once the query store is enabled, you really need to monitor things to make sure the settings work for your workload. It seems people often wonder why it just changes to read only, usually after it hits the 100 MB max size default (that default sizes changes on SQL Server 2019). There are some guidelines in the following documentation:

    Best Practice with the Query Store

    Sue

  • TRACEY-320982

    SSChampion

    Points: 13480

    Thank you

  • Grant Fritchey

    SSC Guru

    Points: 395394

    Monitoring Query Store through Extended Events is a good way to keep an eye on how it's behaving. For a whole bunch of info on Query Store, I'd like to recommend this book. I helped write it. It's got a ton of info along these lines.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply