January 29, 2023 at 7:57 pm
Hello!
I am new to Query Store feature, once this is enabled it stores data captured in the database.
If I set this policy below, will it purge data once it reaches the 1 GB or keeps the data till 7 days before it purges? Will enabling cause additional system overhead?
Query Store Retention
Thanks in advance.
January 30, 2023 at 10:57 am
Check
It will switch to readonly when the storage limit is reached. It will a bit of system overhead but not much. Currently one of the best ways to identify troublesome queries (because of the low overhead)
January 30, 2023 at 2:17 pm
With size based cleanup set to auto, it'll ignore the 7 day limit and clean up the data based on the size.
EDIT!
But, if it doesn't hit the size limit, then the 7 day limit takes over for cleanup. Sorry I left that out.
"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
January 30, 2023 at 11:23 pm
Thank you for the reply, so does it mean it will be available for 7 days once it reaches it 1 GB limit before it gets cleaned up?
January 31, 2023 at 2:03 pm
No. If you have size cleanup enabled, it'll clean up when it runs out of room, not wait the 7 days first.
You get a time-based cleanup and a size-based cleanup. Enabling the automatic cleanup based on size, means, if the size limit is hit before the time limit, you'll get a cleanup. Disabling the size cleanup means you'll only get a time cleanup.
It's all about choices and options. Is it more important that you're capturing queries today, or that queries from seven days ago are always available. If you want to be sure you're capturing as much current data as possible, you'll enable size-based cleanup. If what really matters is the older queries, and you don't mind that it's not collecting information today (which means, in seven days it also won't be available because it was never captured) because it filled up the space available, then disable size-based cleanup.
Personally, I'd do both. Don't retain the data longer than you need, and yeah, if you run out of space, go ahead and nuke the old data (and it will remove data based on age & activity). Monitor this over time though. You don't want it thrashing really hard, deleting data that's hours old because it's running out of room. You want to strike a balance, enough space so it collected and retains enough data for good evaluations on performance and behavior. Minimum I'd go for most systems is 3 days (to cover a weekend), and then size it accordingly.
"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
February 5, 2023 at 4:25 am
Thanks for the information.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply