Xjyuk

Here is the plan: https://www.brentozar.com/pastetheplan/?id=rkM8d7ONS

I am mostly interested in how to get rid of the lazy spool?

This is the query:

SELECT DISTINCT 
 SM.Security_ID 'Security_ID', 
 Leg.Leg_Type 'Leg_Type', 
 Leg.Leg_Side 'Leg_Side',
 Ct.Security_Type AS 'Swap_Type',
 Leg.CDX_Indicator AS 'CDS_CDX_Flag',
 SM.Currency AS 'Notional_Currency',
 Ct.Cross_Currency_Flag AS 'Cross_Currency_Flag',
 Ct.Custom_Overrides AS 'Special_Instructions',
 Leg.Protection_Indicator AS 'Buy_Sell_Protection',
 Leg.Commission_Direction AS 'Commission_Direction',
 Leg.Dividend_Payment_Indicator AS 'Undl_Asset_Dividend_Flag',
 SM.Issue_Date AS 'Effective_Date',
 SM.Maturity_Date AS 'Maturity_Date',
 Leg.Settlement_Frequency 'Settlement_Frequency',
 Leg.Reset_Frequency 'Reset_Frequency',
 Leg.Roll_Day AS 'Roll_Day',
 Leg.Reset_Business_Day_Convention AS 'Reset_Business_Day_Convn',
 Leg.Settlement_Business_Day_Convention AS 'Settlement_Business_Day_Convn',
 Leg.First_Payment_Date AS 'First_Period_End_Date',
 Leg.Day_Count AS 'Day_Count_Basis',
 Leg.Interest_Rate AS 'Interest_Rate',
 Leg.Spread AS 'Spread',
 Leg.CDX_Attachment AS 'CDX_Attachment', 
 Leg.CDX_Detachment AS 'CDX_Detachment', 
 Leg.Factor AS 'Factor',
 Leg.Commission AS 'Commission', 
 Leg.Reset_Lag AS 'Reset_Lag', 
 Leg.Initial_Index_Price AS 'Initial_Price',
 Ct.Principal_Exchange_Initial AS 'Principal_Exchange_Initial',
 Ct.Principal_Exchange_Final AS 'Principal_Exchange_Final',
 IsNull(Leg.Delay_Days, 0) AS 'Settlement_Delay_Days',
 Leg.Red_Code AS 'Red_Code',
 Leg.Referenced_Asset AS 'Referenced_Asset',
 SM.Short_Description AS 'Security_Description',
 Leg.Notional_Reset_Type AS 'Notional_Reset_Type',
 Leg.Reset_Arrears_Flag AS 'Reset_Arrears_Flag',
 SM.MIC AS 'Position_Market',
 SM.Currency AS 'Position_Currency',
 1,
 Ct.Security_Id,
 Txn.CLEARED_TRD_INDICATOR
 FROM 
 ##AssetAddSwap_Tbl S
 INNER JOIN Sch_Core_Data.Security_Master SM (NOLOCK) ON S.Security_ID = SM.Security_ID
 INNER JOIN Sch_Core_Data.Security_Detail_SwapLeg Leg (NOLOCK) ON SM.Security_Id = Leg.Security_Id
 INNER JOIN Sch_Core_Data.Security_Detail_SwapContract Ct (NOLOCK) ON Leg.Contract_Security_Id = Ct.Security_Id
 LEFT JOIN Sch_Core_Data.VW_TRANSACTIONS_Abbreviated Txn WITH (NOLOCK) ON Leg.Security_ID = Txn.Security_ID
 LEFT JOIN Sch_Core_Data.Security_Alt_Identifier SAI (NOLOCK) ON SAI.Security_Id = Leg.Security_Id 
 AND SAI.Identifier_Type = 'APXID' AND SAI.STATUS_FLAG = 'ACT'
 WHERE 
 S.Retransmit_Flag = 1 
 OR (--Txn.Transaction_ID IS NOT NULL
 --AND Txn.TRANSACTION_SOURCE_SYSTEM <> @TargetSystem
 Txn.TRANSACTION_SOURCE_SYSTEM NOT IN (SELECT svalue from SCH_CORE_DATA.DBL WHERE PROCESS = 'SYSTEMS' AND [FUNCTION] = 'LIST' AND DESCRIPTION3 = 'NON-TOM')
 AND Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1
 AND Sch_Core_Code.Udf_Chk_BitValue(Txn.Transmitted, 3) = 0 
 AND IsNull(SAI.Identifier, '') = '')

Using this function:

-- =============================================
-- Author: Anuj Kalra
-- =============================================
CREATE FUNCTION [SCH_CORE_CODE].[UDF_CHK_BITVALUE]
(
@VALUE INT,
@FIND INT
)
RETURNS BIT
WITH EXECUTE AS 'USR_CORE'
AS
BEGIN
 DECLARE @CHECK BIT;
 SET @CHECK = 0; 
 IF ((POWER(2,@FIND) & @VALUE)<> 0) 
 BEGIN 
 SET @CHECK = 1; 
 END
 RETURN @CHECK;

The use of a scalar user-defined function is inhibiting parallelism in this query, which is hinted at in the execution plan XML: NonParallelPlanReason="CouldNotGenerateValidParallelPlan"

One option would be to manually inline this function's implementation. So this:

Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1

Becomes this:

CASE WHEN (POWER(2,3) & Txn.Account_Subscription)<> 0 THEN 1 ELSE 0 END = 1

Note: you would need to do this for both references to the function

There are some large index scans in the execution plan (22 million rows are read from the ACCOUNT table). If the query really needs to process this many rows, parallelism can help a lot.

If the DISTINCT is not required for correct results, removing that could help with performance as well (preventing the potentially costly Sort (Distinct Sort) in the plan.

As far as the spool is concerned, it might actually be helping in this case. You can try suppressing the spool by adding OPTION (QUERYTRACEON 8690) to the end of the query and seeing how the plan / query execution time is different. On SQL Server 2016 onward, the query hint NO_PEROFRMANCE_SPOOL is available.