I am using Azure SQL 18 vCore db with Forced Parameterization, and MAXDOP 8. The vCore count may change much lower for other dbs but for the same query. Running sp_blitzcache has shown a query with the following warnings:
Compilation Timeout, Forced Parameterization, Multiple Plans (11), Plan created last 4hrs, Long Running With Low CPU, Many Rows Table Spool, non-SARGables
The main table has about 600Krows, and the two supporting tables have about 200 rows.
I'm a newbie curious about a number of these things. There are about 2700 plans in the cache currently for this query_hash, all using the same query_plan_hash. Compilation times out a very large portion of the time (can't confirm that it's every time). I'm about to turn on traceflag 4199 in hopes of getting rid of the timeouts, and the performance of the query is acceptable within the app context that I know of, but I'd like to understand what is stumping the compiler. Thanks for any help on this!