0

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 ?

  • Any database backup or maintenance jobs (like index rebuild/re-org) running at night while not in daytime? – jyao Aug 19 '16 at 18:42
  • no maintenance jobs or backups jobs running around that time except a processes from another database. I believe that is what causing the issue in disk subsystem. – Robin16 Aug 19 '16 at 19:27
2

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.

  • actually what you mentioned makes sense, i'm seeing PLE going down around that, from 16000 to 3000. but why the data is in the memory during day time and why it isn't during night time ? is it because of other loads happening on another database ? Other session does so many DML operations too. – Robin16 Aug 19 '16 at 19:44
  • Yes likely your other maintenance on other databases is pushing the data out of the buffer pool, thus requiring this process to have to get the data from disk and then put it into memory. With your disks having I/O slowness, retrieving the data pages from disk is taking a long time. – Tara Kizer Aug 19 '16 at 19:46

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.