INSERT INTO #QueryDetailsStagingTable Does Not Scale

[DatabaseJase]

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

Line 755 in 115f671

-- Detailed Report: Use @SqlCmd load details into #QueryDetailsStagingTable - START

I have added an OPTION (FORCE ORDER) to this statement because when there is a high volume of data in [sys].[query_store_runtime_stats] the execution plan does not scan the #DeleatableQueryTable resulting in a query that executes for over 12 hours without completing. the OPTION (FORCE ORDER) allows the query to run in a reasonable amount of time.

I did not want to use the OPTION (FORCE ORDER) solution so I have re-written the SELECT as a CTE to generate a query plan that executes in 15 minutes with the same volume of data.