Issue while running the [dbo].[QDSCacheCleanup] to purge Query Store

[bcboy84]

Hi,

There is an issue while trying to purge Query store.

Ran below, and the proc is running for hours and it ran for more than 10 or 12 hours and finally I cancelled it.
Please advise if there is anything we can do to fix.

EXECUTE [dbo].[QDSCacheCleanup]
@DatabaseName = 'DB'
,@CleanAdhocStale = 0
,@CleanStale = 1
,@retention = 12
,@MinExecutionCount = 2
,@CleanOrphan = 1
,@CleanInternal = 1
,@CleanStatsOnly = 0

[SQLozano]

• Is this the first time the cleanup process gets stuck?
• How big was the cache (rowcount of sys.query_store_runtime_stats)?
• Was there any blocking noticed when the cleanup process was running?
• Could you try rerunning the same parameters but adding @Testmode = 1 and @verbosemode = 1 to try and understand which one of the steps seemed to be handing?

[bcboy84]

@SQLozano
I have run the clean up few times in production and it never seems to complete
sys.query_store_runtime_stats is having 736056 rows
Size of the Query store used is around 10 GB out of total of 20 GB

On running the @verbosemode = 1 on UAT server (where query store size is 1 GB) it seems to be running for a long time. However, I can see that it is performing some thing.

Is there any way we can fasten this process up?

[DatabaseJase]

I am having a similar issue and I think the problem is the performance of the Microsoft stored procedures that perform the cleanup, and not an issue with QDSCacheCleanup directly.

As a guide it took 8 hours to clean up 186,639 queries (this was stats only cleanup as the default is incorrect in the comments of the stored procedure (see another issue I raised).

sys.query_store_query = 1,346,106
sys.query_store_runtime_stats = 1,394,532

I propose a new parameter (I suggest a default of 25,000 as that seems to be the tipping point for long execution times, so run time is about 1 hour based on my statistics):

@MaxCleanupQueryCount INT = 25000

Plus a variable @CleanUpQueryCount to hold a counter that is incremented each loop.

The Loop Condition is then updated to evaluate the new parameter:

WHILE (SELECT COUNT(1) FROM #DeleteableQueryTable) > 0 AND (@MaxCleanupQueryCount = 0 OR @CleanUpQueryCount < @MaxCleanupQueryCount)

qdstoolbox/QDSCacheCleanup/3-dbo.QDSCacheCleanup.Procedure.sql

Line 913 in 115f671

WHILE (SELECT COUNT(1) FROM #DeleteableQueryTable) > 0

qdstoolbox/QDSCacheCleanup/3-dbo.QDSCacheCleanup.Procedure.sql

Line 956 in 115f671

WHILE (SELECT COUNT(1) FROM #DeleteableQueryTable) > 0

This will allow a fixed number of queries to be removed each time and prevent the long execution times.

[DatabaseJase]

I picked up that this statement can take up to 300ms to run (each times it loops) with a high volume of data, so adding an index on [ForcedPlan] will help (it has in my testing):

qdstoolbox/QDSCacheCleanup/3-dbo.QDSCacheCleanup.Procedure.sql

Line 916 in 115f671

;WITH dqt AS ( SELECT TOP(1) * FROM #DeleteableQueryTable ORDER BY [ForcedPlan] DESC)