question

sakuraime avatar image
0 Votes"
sakuraime asked ·

Query store STALE_QUERY_THRESHOLD_DAYS and SIZE_BASED_CLEANUP_MODE

If I have a query store setting
STALE_QUERY_THRESHOLD_DAYS 30 days

SIZE_BASED_CLEANUP_MODE 100MB


suppose there are 30 days query already inside the query store and used up 50MB only ,

and after 10 days the size reach to 60MB , will it do the housekeep of the oldest 10 days query store records?

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

Yes. I have not actually played with it, but my reading of the docs is that SIZE_BASED_CLEANUP_MODE is a way to avoid that you outgrow the space allotted for Query Store so that Query Store goes into read-only mode. The main cleanup policy is by time.

If you want to know for sure, run your own tests (with a low value for stale queries, so that you don't have to wait for a month for the outcome!=

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks. let me do some test .

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

Hi sakuraime,

The Size Based Cleanup Mode option is for cleaning the SQL Server Query Store data when the maximum size in the Max Size (MB) option is reached to 90% of capacity. The cleanup process stops when 80% of the maximum size in the Max Size (MB) option is reached. Please refer to this article which might help.

Best Regards,
Amelia


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"cleanup process stops when 80%" << so how does the cleanup actually related to the STALE_QUERY_THRESHOLD_DAYS setting ?

I want to understand when 90% size is reach , what will be chosen to be clean up ?

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

Hi sakuraime,

The Size Based Cleanup Mode is more related to Max Size (MB) option. There is a possibility for data that has not reached the Stale Query Threshold (Days) option to be removed. (e.g. if MAX_STORAGE_SIZE_MB is 2GB and STALE_QUERY_THRESHOLD_DAYS is 30 days, and you reach 2GB in 15 days, data will start to be removed). Please refer to Query Store Settings which might help.

what will be chosen to be clean up ?

The cleanup will remove the oldest and least expensive query data.

Best Regards,
Amelia


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"The cleanup will remove the oldest and least expensive query data." <<

so STALE_QUERY_THRESHOLD_DAYS doesn't have any meaning right here ..

suppose I have a query store max size 1GB and STALE_QUERY_THRESHOLD_DAYS=30
If 1GB can hold 60 Days of query stats, the query stats within 30 won't likely to be targeted to delete
If 1GB can not hold 30 Days of query stats, the query stats within 30 will also be deleted


0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

If 1GB can hold 60 Days of query stats, the query stats within 30 won't likely to be targeted to delete

Yes, that's my understanding. But data that is more than 30 days old, will be deleted no matter what.

If 1GB can not hold 30 Days of query stats, the query stats within 30 will also be deleted

Obviously.

But as I said before, if you really want to know - run your own testing.



· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, that's my understanding. But data that is more than 30 days old, will be deleted no matter what. <<< So what trigger this clean up given that the querystore size havent reach to 90% of max size .

??

0 Votes 0 ·

The fact that the data is more than 30 days old. Obviously.

0 Votes 0 ·