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 ?