November 3, 2009 at 11:22 am
I have an SSIS package that loads a database everynight. the package took 45 minutes to run over the years (Sql 2000 to Sql 2005 to Sql 2008). All of a sudden this weekend my package started taking 8 hours to run. I have changed nothing, the server is the same no newupdates have been added.
Has anyone seen this before?
I think this is the query that is cuasing the issue:
INSERT INTO F_Sales_Detail
(date_id, site_id, store_id, sprsn_id, trans_type_id, prod_id, sales_type_id, buyer_id, gross_amt, gp_amt, cost_amt, net_amt, credit_amt, qty_sold, qty_ship, nbr_trans)
SELECT D_Dates.date_id, D_Site.Site_id, D_Store.store_id, D_Salespeople.sprsn_id, D_Trans_Type.trans_type_id, D_Product.Prod_id, D_Sales_Type.sales_type_id, D_Buyer.buyer_id, tmp_F_Sales_Detail.gross_amt, tmp_F_Sales_Detail.gp_amt, tmp_F_Sales_Detail.cost_amt,
tmp_F_Sales_Detail.net_amt, tmp_F_Sales_Detail.credit_amt, tmp_F_Sales_Detail.qty_sold, tmp_F_Sales_Detail.qty_ship, tmp_F_Sales_Detail.nbr_trans
FROM tmp_F_Sales_Detail INNER JOIN
D_Trans_Type ON tmp_F_Sales_Detail.trans_type_id = D_Trans_Type.trans_type_num INNER JOIN
D_Salespeople ON tmp_F_Sales_Detail.sprsn_id = D_Salespeople.sprsn_num INNER JOIN
D_Buyer ON tmp_F_Sales_Detail.buyer_id = D_Buyer.buyer_num INNER JOIN
D_Site ON tmp_F_Sales_Detail.site_id = D_Site.site_sys_num INNER JOIN
D_Dates ON tmp_F_Sales_Detail.date_id = D_Dates.dt INNER JOIN
D_Product ON tmp_F_Sales_Detail.prod_id = D_Product.Internal_product_code INNER JOIN
D_Store ON tmp_F_Sales_Detail.store_id = D_Store.store_num INNER JOIN
D_Sales_Type ON tmp_F_Sales_Detail.sales_type_id = D_Sales_Type.sales_type_num
November 3, 2009 at 1:21 pm
Was there an increase in the amount of data it's handling?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 1:25 pm
No, on average there is about 10 000 to 20 000 records a day
November 3, 2009 at 3:04 pm
The number of joins in that query is indeed impressive. I think the execution plan was changed. Did you add or drop any indexes recently?
November 3, 2009 at 6:06 pm
Im loading a datawarehouse with many dimension tables.
WHen I moved to sql 08, I added more indexes but things were working well for a month.
My issue just started. When I ran accross this issue, I decided to drop the indexes and try again. But noting changed.
November 3, 2009 at 9:07 pm
JonJon (11/3/2009)
Im loading a datawarehouse with many dimension tables.WHen I moved to sql 08, I added more indexes but things were working well for a month.
My issue just started. When I ran accross this issue, I decided to drop the indexes and try again. But noting changed.
You do know that this statement counterdictes your statement in your original post about no changes.
Have you forced a recompile of the procedures used to do the imports, or updated the statistics? If not, you may want to give it a try.
November 4, 2009 at 11:10 am
Thank You Thnak You Thank You to all who assisted.
The issue was dealing with INDEX Fragmentation.
I ran the sys.dm_db_index_physical_stats and found my indexes fragmented.
using the DBCC DBREINDEX solved the issue.
Thank sagain.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply