Forced Parameterization generating duplicate plans

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply