question

knsk_bsk avatar image
knsk_bsk asked

SQL Server 2016 Query Store behavior

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 flush 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?

Apart from this what does the query store read only reason 327680 mean?

I am not able to get anything for 327680 in MS Documentation

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql?view=sql-server-ver15

sql server 2016
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
MikeyBronowski avatar image
MikeyBronowski answered

Regarding the readonly_reason, based on that link

readonly_reason returns a bit map to indicate why the Query Store is in readonly mode.


So


65536 - the Query Store has reached the size limit set by the MAX_STORAGE_SIZE_MB option. For more information about this option, see ALTER DATABASE SET options (Transact-SQL).

262144 - Size of in-memory items waiting to be persisted on disk has reached the internal memory limit. Query Store will be in read-only mode temporarily until the in-memory items are persisted on disk.


65536 + 262144 = 327680 which is exactly what you've got.


1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

knsk_bsk avatar image knsk_bsk commented ·

Thanks...This seems to be quite helpful.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.