Recently I have come across a situation where the max size was set at 5 TB and Query Store was showing around 30 TB.
Some of the forums that I came across says that size based cleanup does not work in SQL 2016 and 2017 when you have large number of adhoc parameterized queries running (i.e. large number of queryid s for same queryhash)
I could see a very large number of queries against a particular queryhash, however, I am unable to understand that my system can have tons of adhoc queries that are logically identical to have same hash, but why would query store get stuck to do what is supposed to do.
our settings are
data flusjh interval 60
statistics collection interval 1hour
max size 5TB
query store capture mode auto
size based cleanup mode auto
stale query threshold days 60
SQL server version Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461)
does anyone have any insights?
