1

A stored procedure I am running is failing when it fills up the tempdb (32GB). It fails after around 1 hour.

The stored procedure is below:

/****** Object:  StoredProcedure [Reporting].[Merge_Fact_Customer_Engagement]    Script Date: 23/08/2019 14:32:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [Reporting].[Merge_Fact_Customer_Engagement]
AS


IF OBJECT_ID('tempdb..#CustEng_stg') IS NOT NULL
    DROP TABLE #CustEng_stg;
IF OBJECT_ID('tempdb..#CustEng_delta') IS NOT NULL
    DROP TABLE #CustEng_delta;                                                                                      


SELECT

-- Business Key                                                                                                 
    [Customer_Engagement_Id]        

-- Foreign Keys                                         
    ,cec.[Customer_Engagement_Context_Key]                                                  -- Engagement_Type_Id                               
    ,d.[Date_Key]                           AS [Engagement_Date_Key]                        -- Engagement_Date                                  
    ,t.[TimeKey]                            AS [Engagement_Time_Key]                        -- Engagement_Time                                  
    ,c.[Contact_Key]                                                                        -- LBS_Number                                       
    ,age.[Age_Key]                          AS [Age_On_Engagement_Key]                      -- Age_On_Engagement                                
    ,pic.[Programme_Interest_Context_Key]                                                   -- Programme_Interest_Status_Code                   
    ,cnt1.[Country_Key]                     AS [Country_Of_Nationality_Key]                 -- Country_Of_Nationality_Id                        
    ,cnt2.[Country_Key]                     AS [Second_Country_Of_Nationality_Key]          -- Second_Country_Of_Nationality_Id             
    ,cnt3.[Country_Key]                     AS [Country_Of_Residency_Key]                   -- Country_Of_Residency_Id                          
    ,cnt4.[Country_Key]                     AS [Country_Lived_In_Key]                       -- Country_Lived_In_Id                              
    ,cnt5.[Country_Key]                     AS [Event_Country_Key]                          -- Event_Country_Id                             
    ,pt.[Programme_Type_Key]                                                                -- Programme_Type_Code                              
    ,p.[Programme_Key]                                                                      -- Programme_Code                                   
    ,camp1.[Campaign_Key]                   AS [Marketing_Campaign_Key]                     -- Marketing_Campaign_Id                            
    ,camp2.[Campaign_Key]                   AS [List_Load_Campaign_Key]                     -- List_Load_Campaign_Id                            
    ,camp3.[Campaign_Key]                   AS [Event_Campaign_Key]                         -- Event_Campaign_Id                                
    ,camp4.[Campaign_Key]                   AS [Trigger_Marketing_Campaign_Key]             -- Trigger_Marketing_Campaign_Id                    
    ,d2.[Date_Key]                          AS [Score_Removed_Date_Key]                     -- Score_Removed_Date       
    ,a.[Account_Key]                                                                        -- Account_Id       

-- Metrics
    ,ce.[Programme_Interest_Score]                                      
    ,ce.[Customer_Engagement_Score]                                 
    ,ce.[Customer_Engagement_Default_Score]                         
    ,ce.[Multi_Campaign_Attribution_Flag]                                                                   
    ,ce.[New_Contact_Flag]                                                                      
    ,ce.[Customer_Engagement_Count]                                                                 
    ,ce.[Entity_Record_Source]                                                                                          



    ,CONVERT(BIGINT, HASHBYTES('SHA1', CONCAT(
                                            [Customer_Engagement_Id] ,cec.[Customer_Engagement_Context_Key] ,d.[Date_Key] ,t.[TimeKey] ,c.[Contact_Key],age.[Age_Key],pic.[Programme_Interest_Context_Key] ,cnt1.[Country_Key] ,cnt2.[Country_Key] ,cnt3.[Country_Key] ,cnt4.[Country_Key] ,cnt5.[Country_Key] ,pt.[Programme_Type_Key] ,p.[Programme_Key] ,camp1.[Campaign_Key] ,camp2.[Campaign_Key] ,camp3.[Campaign_Key] ,camp4.[Campaign_Key] ,d2.[Date_Key] ,a.[Account_Key] ,ce.[Programme_Interest_Score] ,ce.[Customer_Engagement_Score] ,ce.[Customer_Engagement_Default_Score] ,ce.[Multi_Campaign_Attribution_Flag] ,ce.[New_Contact_Flag] ,ce.[Customer_Engagement_Count] ,ce.[Entity_Record_Source] --,[Age_On_Engagement_Key]

    ))) AS HashId


INTO   #CustEng_stg
FROM   [Integration].[Customer_Engagement] ce

LEFT OUTER JOIN [Reporting].[Dim_Contact] c
    ON ce.[LBS_Number] = c.[LBS_Number]
    AND CONVERT(date,CONVERT(varchar,[Engagement_Date])) BETWEEN c.[Row_Effective_Date] AND c.[Row_Expiry_Date]

LEFT OUTER JOIN [Reporting].[Dim_Customer_Engagement_Context] cec
    ON ce.[Engagement_Type_Id] = cec.[Engagement_Type_Id]

LEFT OUTER JOIN [Reporting].[Dim_Programme_Interest_Context] pic
    ON ce.[Programme_Interest_Status_Code] = pic.[Programme_Interest_Status_Code]

LEFT OUTER JOIN [Reporting].[Dim_Campaign] camp1
    ON ce.[Marketing_Campaign_Id] = camp1.[Campaign_Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Campaign] camp2
    ON ce.[List_Load_Campaign_Id] = camp2.[Campaign_Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Campaign] camp3
    ON ce.[Event_Country_Id] = camp3.[Campaign_Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Campaign] camp4
    ON ce.[Trigger_Marketing_Campaign_Id] = camp4.[Campaign_Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Programme] p
    ON ce.[Programme_Code] = p.[Programme_Code]
    AND CONVERT(date,CONVERT(varchar,[Engagement_Date])) BETWEEN p.[Row_Effective_Date] AND p.[Row_Expiry_Date]

LEFT OUTER JOIN [Reporting].[Dim_Programme_Type] pt
    ON ce.[Programme_Type_Code] = pt.[Programme_Type_Code]

LEFT OUTER JOIN [Reporting].[Dim_Account] a
    ON ce.[Account_Id] = a.[Account_Id]
    AND CONVERT(date,CONVERT(varchar,[Engagement_Date])) BETWEEN a.[Row_Effective_Date] AND a.[Row_Expiry_Date]

LEFT OUTER JOIN [Reporting].[DimDate] d
    ON ce.[Engagement_Date] = d.[Date_Key]

LEFT OUTER JOIN [Reporting].[DimDate] d2
    ON ce.[Score_Removed_Date] = d.[Date_Key]

LEFT OUTER JOIN [Reporting].[DimTime] t
    ON ce.[Engagement_Time] = t.[TimeKey]

LEFT OUTER JOIN [Reporting].[Dim_Country] cnt1                                      
    ON ce.[Country_Of_Nationality_Id] = cnt1.[Country_Id]                                   

LEFT OUTER JOIN [Reporting].[Dim_Country] cnt2                                              
    ON ce.[Second_Country_Of_Nationality_Id] = cnt2.[Country_Id]                                                    

LEFT OUTER JOIN [Reporting].[Dim_Country] cnt3
    ON ce.[Country_Of_Residency_Id] = cnt3.[Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Country] cnt4
    ON ce.[Country_Lived_In_Id] = cnt4.[Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Country] cnt5
    ON ce.[Event_Country_Id] = cnt5.[Country_Id]

LEFT OUTER JOIN [Reporting].[Dim_Age] age
    ON ce.[Age_On_Engagement] = age.[Age]

SELECT *
INTO   #CustEng_delta
FROM   #CustEng_stg AS s
WHERE  NOT EXISTS (SELECT 1
                    FROM   [Reporting].[Fact_Customer_Engagement] AS a
                    WHERE  a.HashId = s.HashId);

IF OBJECT_ID('tempdb..#CustEng_stg') IS NOT NULL
    DROP TABLE #CustEng_stg;

MERGE INTO Reporting.Fact_Customer_Engagement AS T

USING #CustEng_delta AS S ON (S.[Customer_Engagement_Id] = T.[Customer_Engagement_Id])

WHEN MATCHED THEN UPDATE 

SET 

        T.[Customer_Engagement_Context_Key]             =   S.[Customer_Engagement_Context_Key]                     
    ,T.[Engagement_Date_Key]                            =   S.[Engagement_Date_Key]             
    ,T.[Engagement_Time_Key]                            =   S.[Engagement_Time_Key]             
    ,T.[Contact_Key]                                    =   S.[Contact_Key]                     
    ,T.[Age_On_Engagement_Key]                          =   S.[Age_On_Engagement_Key]           
    ,T.[Programme_Interest_Context_Key]                 =   S.[Programme_Interest_Context_Key]      
    ,T.[Country_Of_Nationality_Key]                     =   S.[Country_Of_Nationality_Key]          
    ,T.[Second_Country_Of_Nationality_Key]              =   S.[Second_Country_Of_Nationality_Key]   
    ,T.[Country_Of_Residency_Key]                       =   S.[Country_Of_Residency_Key]            
    ,T.[Country_Lived_In_Key]                           =   S.[Country_Lived_In_Key]                
    ,T.[Event_Country_Key]                              =   S.[Event_Country_Key]                   
    ,T.[Programme_Type_Key]                             =   S.[Programme_Type_Key]                  
    ,T.[Programme_Key]                                  =   S.[Programme_Key]                       
    ,T.[Marketing_Campaign_Key]                         =   S.[Marketing_Campaign_Key]              
    ,T.[List_Load_Campaign_Key]                         =   S.[List_Load_Campaign_Key]              
    ,T.[Event_Campaign_Key]                             =   S.[Event_Campaign_Key]                  
    ,T.[Trigger_Marketing_Campaign_Key]                 =   S.[Trigger_Marketing_Campaign_Key]      
    ,T.[Score_Removed_Date_Key]                         =   S.[Score_Removed_Date_Key]              
    ,T.[Account_Key]                                    =   S.[Account_Key]                     
    ,T.[Programme_Interest_Score]                       =   S.[Programme_Interest_Score]            
    ,T.[Customer_Engagement_Score]                      =   S.[Customer_Engagement_Score]           
    ,T.[Customer_Engagement_Default_Score]              =   S.[Customer_Engagement_Default_Score]   
    ,T.[Multi_Campaign_Attribution_Flag]                =   S.[Multi_Campaign_Attribution_Flag] 
    ,T.[New_Contact_Flag]                               =   S.[New_Contact_Flag]                    
    ,T.[Customer_Engagement_Count]                      =   S.[Customer_Engagement_Count]
    ,T.[HashId]                                         =   S.[HashId]
    ,T.[Last_Updated_Date_Time]                         =   GETDATE()           
    ,T.[Entity_Record_Source]                           =   S.[Entity_Record_Source]                




WHEN NOT MATCHED THEN 


INSERT ([Customer_Engagement_Id], [Customer_Engagement_Context_Key], [Engagement_Date_Key], [Engagement_Time_Key], [Contact_Key], [Age_On_Engagement_Key], [Programme_Interest_Context_Key], [Country_Of_Nationality_Key], [Second_Country_Of_Nationality_Key], [Country_Of_Residency_Key], [Country_Lived_In_Key], [Event_Country_Key], [Programme_Type_Key], [Programme_Key], [Marketing_Campaign_Key], [List_Load_Campaign_Key], [Event_Campaign_Key], [Trigger_Marketing_Campaign_Key], [Score_Removed_Date_Key], [Account_Key], [Programme_Interest_Score], [Customer_Engagement_Score], [Customer_Engagement_Default_Score], [Multi_Campaign_Attribution_Flag], [New_Contact_Flag], [Customer_Engagement_Count], [HashId], [Last_Updated_Date_Time], [Entity_Record_Source])

VALUES

        (    S.[Customer_Engagement_Id]
            ,S.[Customer_Engagement_Context_Key]        
            ,S.[Engagement_Date_Key]                
            ,S.[Engagement_Time_Key]                
            ,S.[Contact_Key]                        
            ,S.[Age_On_Engagement_Key]                                              
            ,S.[Programme_Interest_Context_Key]     
            ,S.[Country_Of_Nationality_Key]         
            ,S.[Second_Country_Of_Nationality_Key]  
            ,S.[Country_Of_Residency_Key]           
            ,S.[Country_Lived_In_Key]               
            ,S.[Event_Country_Key]                  
            ,S.[Programme_Type_Key]                 
            ,S.[Programme_Key]                      
            ,S.[Marketing_Campaign_Key]             
            ,S.[List_Load_Campaign_Key]             
            ,S.[Event_Campaign_Key]                 
            ,S.[Trigger_Marketing_Campaign_Key]     
            ,S.[Score_Removed_Date_Key]             
            ,S.[Account_Key]                        
            ,S.[Programme_Interest_Score]           
            ,S.[Customer_Engagement_Score]          
            ,S.[Customer_Engagement_Default_Score]  
            ,S.[Multi_Campaign_Attribution_Flag]    
            ,S.[New_Contact_Flag]                   
            ,S.[Customer_Engagement_Count]
            ,S.[HashId]
            ,GETDATE()          
            ,S.[Entity_Record_Source]
        )
        ;
IF OBJECT_ID('tempdb..#CustEng_stg') IS NOT NULL
    DROP TABLE #CustEng_stg;
IF OBJECT_ID('tempdb..#CustEng_delta') IS NOT NULL
    DROP TABLE #CustEng_delta;

The problem is in the first INSERT INTO statement. Estimated execution plan is below:

https://www.brentozar.com/pastetheplan/?id=H1Z0hPp4B

I'm not sure if the JOINS are causing the issue.

EDIT:

The issue is in the joins, there are a few many-to-many relationships I hadn't catered for so I am going through and working out where the culprits are. Thank you for all the answers, will post again when I get it fixed!

EDIT 2:

Updated query plan - those joins were very very wrong, but it's fixed now! https://www.brentozar.com/pastetheplan/?id=H1dV2OaVB

Improve this question
11
  • 1
    How many rows does that select statement return? The estimated plan has a absurdly high estimated row count for the insert. Commented Aug 23, 2019 at 13:45
  • It hasn't been able to execute in full so I'm not sure. I think there must be some many-to-many issue in one of the joins. Currently in the process of doing row counts when including a join one by one. Commented Aug 23, 2019 at 13:47
  • Running a convert on multiple join criteria is gonna hurt a lot, and running a seemingly large concatenation of fields into a hash on top of it? yeesh. Commented Aug 23, 2019 at 13:48
  • 1
    Well, the plan shows Estimated Number of Rows 6.56525E+15 Estimated Row Size 9 B. It is a lot of rows and bytes. Way more than 32GB. Commented Aug 23, 2019 at 13:50
  • 1
    The estimated plan is saying 6.525E+15 rows. You need to go back to the beginning here and figure out what you are trying to do. Also find a way to filter your initial query so you don't insert everything into a temp table and then insert some of that data into another temp table that you are going to use. Commented Aug 23, 2019 at 13:50

2 Answers 2

Reset to default
1

Congratulations that Matt Lakin's problem is fixed now. Thanks for all the helps in comment.

  1. The issue is many-to-many joins.
  2. Matt Lakin has fixed it now, to see the Updated query plan: https://www.brentozar.com/pastetheplan/?id=H1dV2OaVB

I help Matt Lakin post this as answer and hope this can be beneficial to other community members.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The issue I was having was in the JOINs criteria, some of the 'ON' matches were creating many-to-many joins which created a large amount of duplicate rows, as can be seen in the estimated rows in the first execution plan.

I fixed the issue by running COUNT() queries against the base table and then comparing the result to a COUNT() when subsequently adding a JOIN, e.g:

  1. Take base count
   SELECT COUNT(*)

   FROM   [Integration].[Customer_Engagement] ce
  1. Take count including the first JOIN
   SELECT COUNT(*)

   FROM   [Integration].[Customer_Engagement] ce

   LEFT OUTER JOIN [Reporting].[Dim_Contact] c
   ON ce.[LBS_Number] = c.[LBS_Number]
   AND CONVERT(date,CONVERT(varchar,[Engagement_Date])) BETWEEN c.[Row_Effective_Date] 
   AND c.[Row_Expiry_Date]

  1. Compare Counts between (1) and (2). If the count is higher in (2), then there is an issue with the join!

  2. Continue for rest of the joins, noting where counts increase

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.