3

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;
  • Welcome to SO! Is your ultimate goal to identify why your query is slow and how to improve it? If so, include your DDL scripts for your table definitions and indexes and some information about your environment. Good job on providing the plan and researching lazy spool – scsimon Aug 19 at 14:12
  • Your biggest cost is a distinct sort (I/O cost = 10375.6). Why are you worried about the lazy spool (I/O cost = 0.1)? It is more than 10,000 times cheaper than the sort (and is only there because of DISTINCT). Have you considered removing DISTINCT? Is it even doing what you think it's doing? – Aaron Bertrand Aug 19 at 14:43
  • Also regarding perf it estimates that it will evaluate the Scalar UDFs 21,498,301 times each. Have you tried replacing Sch_Core_Code.Udf_Chk_BitValue(Txn.Account_Subscription, 3) = 1 with Txn.Account_Subscription & 8 =8(and same for the other one) to see what hit those are causing you? – Martin Smith Aug 19 at 15:54
  • 2
    Aaron - My understanding about the lazy spool is that often the metric you see in the query plan is not really representative of the actual expense of the lazy spool. I may be able to remove the distinct. I am not sure yet. – Jeff Aug 19 at 16:48
  • 1
    Hi, Jeff - if you want more detailed help, it would be helpful if you could provide an actual execution plan (the one you linked to is an estimated plan). Also, the definition of the view would be useful VW_TRANSACTIONS_Abbreviated . – Josh Darnell Aug 19 at 18:22
7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.