question

acappelletti avatar image
0 Votes"
acappelletti asked ErlandSommarskog commented

Query Store Purge Query Data

Hi all,
I activate query store on one DB for some days... (I'm on SQL SERVER 2017), and a now I would like to purge all data.

Naturally I stopped (turn off) query store... but either by pressing the button from the graphic interface, or by executing the query

 ALTER DATABASE XXXX SET QUERY_STORE CLEAR;

I see always 4 MB of query store space used. Is it normal this situation?

ALEN


sql-server-general
· 2
5 |1600 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.

4MB is not a lot in a database...

0 Votes 0 ·

Hi Erland.
Yes sure it's nothing... but I'm curious to know if a little part (some MB) are always allocated also on OFF of querystore.
Thanks ALEN, Italy.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

Hi @acappelletti,

Did you refresh the SQL server DB after you purged query store and turned off the query store? Then check the Query Store usage from SSMS UI or through the T-SQL that offered by Ben.

In addition, please read the below blog to better understood the information about clearing the Query Store.

Manually Clearing the Query Store


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".



· 6
5 |1600 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.

Hi!
What do you mean by refresh SQL SERVER?
Yes I checked both ways... from GUI and T-SQL and always remains 4 MB of data... isn't 0 like before start QS features.
ALEN

0 Votes 0 ·

Right click SQL server DB, choose Refresh option.

0 Votes 0 ·

Yes I did...
; )

0 Votes 0 ·
Show more comments
JingyangLi avatar image
0 Votes"
JingyangLi answered acappelletti commented

What about this?
ALTER DATABASE XXXX SET QUERY_STORE CLEAR ALL;

· 3
5 |1600 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... but I think that could be a minimum for query store structure...don't you?
Another my co-worker active and deactive QS and always see this few Mb... maybe it's normal...
ALEN

0 Votes 0 ·

What do you get with this query?

 SELECT actual_state, actual_state_desc, readonly_reason,
     current_storage_size_mb, max_storage_size_mb
 FROM sys.database_query_store_options;


Before and after the CLEAR? Run it before and then do a CLEAR and then run it again. Does it change?

0 Votes 0 ·
acappelletti avatar image acappelletti Ben-Miller-DBAduck ·
 actual_state    actual_state_desc    readonly_reason    current_storage_size_mb    max_storage_size_mb
 0    OFF    0    4    100
0 Votes 0 ·