Query Store Purge Query Data

Alen Cappelletti 931

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

Erland Sommarskog 101.9K Reputation points MVP

2022-04-26T21:58:11.343+00:00

4MB is not a lot in a database...
Alen Cappelletti 931 Reputation points

2022-04-26T22:25:58.21+00:00

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.

Accepted answer

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-04-27T02:57:25.24+00:00

Hi @Alen Cappelletti ,

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".
Please sign in to rate this answer.
Alen Cappelletti 931 Reputation points

2022-04-27T07:32:43.59+00:00

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

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-04-27T07:34:03.983+00:00

Right click SQL server DB, choose Refresh option.

Alen Cappelletti 931 Reputation points

2022-04-27T07:36:59.317+00:00

Yes I did...
; )

CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2022-04-27T07:53:15.05+00:00

I did a test in my environment.

1.Turn on query store.
2.Run some query
3.Check the query store usage
4.Purge the query store
5.Turn off the query store
6.Refresh this SQL DB
7.Check the query store usage, it becomes to 0.

Alen Cappelletti 931 Reputation points

2022-04-27T08:09:51.597+00:00

Yes now it's 0 mb!
The only difference is that I purge the query store before turn it off!

Thanks very much... it was no a problem but more a curiosity!
ALEN

Erland Sommarskog 101.9K Reputation points MVP

2022-04-27T21:22:27.917+00:00

I would guess in one case it was a DELETE operation, which may leave some extents around, whereas in the other case it was like TRUNCATE TABLE which deallocates all extents.
Sign in to comment

1 additional answer

Jingyang Li 5,891 Reputation points

2022-04-26T13:33:30.917+00:00

What about this?
ALTER DATABASE XXXX SET QUERY_STORE CLEAR ALL;
Please sign in to rate this answer.
Alen Cappelletti 931 Reputation points

2022-04-26T14:15:23.307+00:00

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

Ben Miller (DBAduck) 951 Reputation points

2022-04-27T02:15:06.343+00:00

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?

Alen Cappelletti 931 Reputation points

2022-04-27T07:16:09.887+00:00

actual_state actual_state_desc readonly_reason current_storage_size_mb max_storage_size_mb 0 OFF 0 4 100
Sign in to comment