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
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image ErlandSommarskog ·

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

0 Votes 0 ·
acappelletti avatar image acappelletti ErlandSommarskog ·

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 ·

2 Answers

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
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

acappelletti avatar image acappelletti ·

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 ·
Cathyji-msft avatar image Cathyji-msft acappelletti ·

Right click SQL server DB, choose Refresh option.

0 Votes 0 ·
acappelletti avatar image acappelletti Cathyji-msft ·

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
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

acappelletti avatar image acappelletti ·

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 ·
Ben-Miller-DBAduck avatar image Ben-Miller-DBAduck acappelletti ·

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 ·