SQL Server 2016 Query Store

%3CLINGO-SUB%20id%3D%22lingo-sub-2317816%22%20slang%3D%22en-US%22%3ESQL%20Server%202016%20Query%20Store%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3ERecently%20I%20have%20come%20across%20a%20situation%20where%20the%20max%20size%20was%20set%20at%205%20TB%20and%20Query%20Store%20was%20showing%20around%2030%20TB.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESome%20of%20the%20forums%20that%20I%20came%20across%20says%20that%20size%20based%20cleanup%20does%20not%20work%20in%20SQL%202016%20and%202017%20when%20you%20have%20large%20number%20of%20adhoc%20parameterized%20queries%20running%20(i.e.%20large%20number%20of%20query%3CEM%3Eid%20s%20for%20same%20query%3C%2FEM%3Ehash)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20could%20see%20a%20very%20large%20number%20of%20queries%20against%20a%20particular%20queryhash%2C%20however%2C%20I%20am%20unable%20to%20understand%20that%20my%20system%20can%20have%20tons%20of%20adhoc%20queries%20that%20are%20logically%20identical%20to%20have%20same%20hash%2C%20but%20why%20would%20query%20store%20get%20stuck%20to%20do%20what%20is%20supposed%20to%20do.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eour%20settings%20are%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Edata%20flusjh%20interval%2060%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Estatistics%20collection%20interval%201hour%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Emax%20size%205TB%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Equery%20store%20capture%20mode%20auto%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Esize%20based%20cleanup%20mode%20auto%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Estale%20query%20threshold%20days%2060%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESQL%20server%20version%26nbsp%3BMicrosoft%20SQL%20Server%202016%20(SP2-CU15-GDR)%20(KB4583461)%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Edoes%20anyone%20have%20any%20insights%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

0 Replies