August 13, 2019 at 3:37 pm
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
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
Thank you
August 14, 2019 at 11:24 am
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