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 ?