This is one of the procs, which having issues:

/****** Object: UserDefinedTableType [dbo].[Identifier_type]******/

CREATE TYPE [dbo].[Identifier_type] AS TABLE(

[id] [BIGINT] NOT NULL

)

GO

CREATE PROCEDURE [dbo].[WorkerABC_prc]

(@workerIds Identifier_type READONLY)

AS

BEGIN

SELECT swm.SyncWorkerId

FROM SyncWorkerMap swm

INNER JOIN @workerIds w

ON swm.SyncWorkerId...

I collect the actual execution plan from sp_whoisactive @get_plans=1 during execution.

Additionally, I used sp_whoisactive @get_plans=1 to compare plans as well

I tested with SET SHOWPLAN_ALL ON/OFF.

All SPs have Parameters, 5 have User Define Table Type, and 1 with just Varchar.

I tried using OPTION (OPTIMIZE FOR UNKNOWN), and the same result

I do not restart the SQL server in between compatibility level changes. There are no server-level flags.

Additionally, I tested restore DB on a different SQL server with most of the...

I am on CU16

the threshold for parallelism =50

It is run fine as soon as change back to 120

First time after a change to 150