Insert into table is slow in the night but very fast during day time

Question

I have an SSIS package that does an insert into a table and is taking 90 minutes to run but when I run the same package during day time its very slow.

during whole insert process in the night, it is waiting on PAGEIOLATCH_EX wait and during the day time it doesn't wait on anything and runs fast.

I couldn't understand why this is happening. Also during night, there is processes that runs on other database which is not related to this database and that runs for almost 4 hours. Not sure if that is impacting disk and causing slowness overall.

Here's the SQL query...

INSERT INTO dbo.Test (
    Source_System_Cd
    , Account_Id
    , Portfolio_Num
    , Instrument_ID
    , Market_Value_Dt
    , Broker_Dealer_Cd
    , Advisor_Firm_Cd
    , Advisor_Rep_Cd
    , Advisor_Rep_Cd_1
    , Advisor_Rep_Cd_2
    , Advisor_Rep_Cd_3
    , Advisor_Rep_Cd_4
    , Advisor_Rep_Cd_5
    , Strategy_id
    , Strategy_Nm
    , Managed_Product_Fl
    , External_Security_ID
    , Instrument_Type_ID
    , Share_Qty
    , Market_Val
    , Strategy_Family_Id
    , Strategy_Manager_Id
    , Portfolio_Manager_Id
    , Product_Id
    )
SELECT DISTINCT p.Source_System_Cd
    , p.Account_Id
    , ISNULL(p.Portfolio_Num, - 1) AS Portfolio_Num
    , p.Instrument_ID
    , p.Market_Value_Dt
    , p.Broker_Dealer_Cd
    , p.Advisor_Firm_Cd
    , p.Advisor_Rep_Cd
    , aa.Advisor_Rep_Cd_1
    , aa.Advisor_Rep_Cd_2
    , aa.Advisor_Rep_Cd_3
    , aa.Advisor_Rep_Cd_4
    , aa.Advisor_Rep_Cd_5
    , p.Strategy_id
    , p.Strategy_Nm
    , p.Managed_Product_Fl
    , p.External_Security_ID
    , p.instrument_type_id
    , p.share_qty
    , p.market_val
    , p.Strategy_Family_Id
    , p.Strategy_Manager_Id
    , p.Portfolio_Manager_Id
    , p.Product_Id
--select count(*) 
FROM dbo.Test_Stage p
LEFT JOIN Advisor.Account_Test aa
    ON aa.Source_System_Cd = p.Source_System_Cd
        AND aa.Advisor_Firm_Cd = p.Advisor_Firm_Cd
        AND aa.Account_Id = p.Account_Id
LEFT JOIN advisor.Test h
    ON p.Source_System_Cd = h.Source_System_Cd
        AND p.Account_Id = h.Account_Id
        AND p.Portfolio_Num = h.Portfolio_Num
        AND p.Instrument_ID = h.Instrument_ID
        AND p.Market_Value_Dt = h.Market_Value_Dt
WHERE p.Source_System_Cd = 2
    AND h.Source_System_Cd IS NULL
    AND p.Market_Value_Dt = @Business

O drive has the data file of this database and during the whole insert process, O drive is 95% busy and i'm also seeing some IO latency in the disk sub system.

dbo.Test table has 300 million rows.

can anyone please help on this ?

Answer 1

The data isn't in memory when it runs at night, based upon the PAGEIOLATCH wait that you mentioned, but is in memory during the day. Your Page Life Expectancy is probably taking a nosedive at night due to all of the other maintenance that is going on. Having to get the data from disks and especially slow disks is much slower than if it were in memory already. Seems like you're going to need to add more memory since getting the data pages from disk is so slow due to the I/O issues you are experiencing.