I hope someone can give me a direction for investigating a slow package running time. We have created an etl package which select data from one database dump it to another. Usually the package will run in 7 minutes. For the past 4 weeks it tends to run around hour.
I have check all the part of this package and discovered that one of the extract query was very slow to come back.
I ran that query on the source database directly form the server instance, so I could remove any network issue. the query still took 1 hour.
I have found with my colleague that updating the statistics on the four tables used by the query, help sometimes to go back to a mins run but it is not always the case.
we are also redoing indexes every night on these tables.
the query is a select with 2 main union and distinct.
any help will appreciated