Forced Parameterization generating duplicate plans

Question

https://www.brentozar.com/pastetheplan/?id=H1qoS7BSu

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!

• robertys: Thank you, I'll be trying to set up an xevent session to capture on of them. To answer the other question, about MAXDOP, I had been experimenting with setting it manually to 8, after coming across some recommendations that it not be higher regardless of core count. It made the wait stats look much better, which I suppose reflects more efficiency, if not faster execution.
• robertys: Here is the actual execution plan from a manual run. brentozar.com/pastetheplan/?id=rywtjnDBu

Answer 1

You will not be able to turn on Trace Flag 4199 in Azure SQL Server. You will get an error message. In fact, you cannot turn on any trace flags (globally or session scope) in the Azure SQL database. At the time of writing this, there were 24 trace flags enabled with Global Scope for Azure SQL database. You can check the list by running DBCC TRACESTATUS.If you want to mimic the same behavior of TF4199 run the below T-SQL statement.

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON ;

Reference : ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

I suggest you read this article:

Understanding Optimizer Timeout and how Complex queries can be Affected in SQL Server by Joseph Pilov

Plus look at the other hints you are getting from running sp_blitzcache and try to resolve those.

• Many Rows Table Spool

  • Table Spool by Hugo Kornelis

  • Nested Loops Joins and Performance Spools by Paul White

• non-SARGables

  • Non-SARGable Predicates by Brent Ozar

  • The Two Ways to Fix Non-Sargable Queries by Brent Ozar

• robertys: Thanks very much for the info. It will be very useful for dev team, and pretty much confirms that compiler timeouts are there for a a reason, and I shouldn't looking for a quick fix to it.