How can I get rid of this Lazy Spool, or otherwise improve this query's performance?

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'

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_PERFORMANCE_SPOOL is available.

StackExchange Database Administrators Q#245667, answer score: 7