Automatic removal of data from Query Store is based on configuration, notably the
CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS), and
SIZE_BASED_CLEANUP_MODEsettings. While these are detailed in another post, it’s worth reiterating that ideally, size-based cleanup never kicks in. A database’s Query Store should be sized to accommodate N days’ worth of data, where N is set by
CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS). When the size of the Query Store exceeds 90% of
MAX_STORAGE_SIZE_MB, and if
SIZE_BASED_CLEANUP_MODEis set to
AUTO, then clean up will kick in. This algorithm is not efficient, and it runs single-threaded. It looks for queries that are infrequently executed/less important and deletes those, one by one, until the size is less than 80% of
MAX_STORAGE_SIZE_MB. Avoid this type of cleanup if at all possible.
There is a better way. And an even better way thanks to some of my colleagues.