January 23, 2019 at 9:49 am
We have been using the Query Store feature at my place of work for quite some time without issues in many of our production databases.
We have, however, a database where the clean ups are not triggered automatically as we expect.
The QS is configured to retain 30 days of data there and the size based clean up is also enabled.
We noticed the QS keeps growing in the database and as I looked further into the contents of the internal tables, there are queries there that have last executed in June last year.
From running an extended events session tracking all QS cleanup events, we noticed the cleanups are activated for the other dbs in the server but not for this one. There are no failed or skipped events for this database either, just no events at all.
Restoring the same db to a different server triggers a clean up in the normal way after about 15 minutes, but it does take away about 10% of our cpu (this test node is identical to the production one) and it takes about 1.5 hours to complete.
It seems to me, the clean up is somehow hung in this database and we're unable to reproduce the problem anywhere else. Any suggestions?
A manual cleanup performed in the restored test db after disabling the automated ones sounds like the way to go, but using sp_query_store_remove_query, sp_query_store_remove_plan and sp_query_store_reset_exec_stats using the query and plan ids obtained by joining the underlying tables and using as main filter HAVING MAX(query_store_runtime_stats.last_execution_time) < DATEADD (day, -30, GETUTCDATE()) doesn't seem to clean up the QS in the same manner that the automatic clean up does. What I mean by that is that the automated clean up ends up with a 1GB query store while the manual one, using the script, ends up with a 6GB query store. The main difference seems to be in the count of run time stats.The plans, queries and query texts counts are very similar.
If I then re-enable automated clean ups in the manually cleaned database, the left over run time stats are removed and we end up with a query store of a similar size as the one after the automated clean up.
We are running SQL Server 2016 Enterprise SP1 CU7 and this database has always run in Enterprise edition (I am aware of a bug with clean ups in 2016 in editions other than enterprise/developer)
Any help is much appreciated.
January 24, 2019 at 5:57 am
I don't have a clue. Sorry. I'd suggest reporting this to Microsoft as a bug. The main clue that it may be a bug is that a restored database on another server does the cleanup with no intervention required.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 24, 2019 at 10:10 am
Thanks, Grant.
We have logged it. Was chancing my arm in case someone else had seen something similar or had any tips. Never waste a good crisis, I say.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply