Post reply

Query store is set to read only

  • August 13, 2019 at 3:37 pm

    #3671071 

    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));



  • August 13, 2019 at 8:25 pm

    #3671157

    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

  • August 14, 2019 at 1:05 am

    #3671197

    Thank you

  • August 14, 2019 at 11:24 am

    #3671267

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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