I have a SQL job that runs an SSIS package and it basically pulls records from staging table and dumps it into the master table. It used to take around 8 hrs. to run but in these last 10 days or so the run duration has increased and I would like to know if there is a way I can find out where the job is spending more time and what it is waiting on either running a profiler trace or running a tsql script inside a job to capture anything useful. It runs every day except Sat at 11p est.
Sql-server – SQL job using SSIS package takes a long time to run especially in the evenings
sql serversql-server-agentssisssis-2008
Related Question
- Running SSIS Package from SQL Agent Job as Non-Sysadmin User
- Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs
- Sql-server – Issues running SSIS package as SQL Server Agent job
- SSIS – Working Package Fails as a Job Due to Data Flow Connection
- SQL Server – SSIS Package Hangs When Running PowerShell Script
Best Answer
Though not really enough info here to give a full answer these steps are how I would approach trouble shooting this...
check the execution logs for the package. Take note of the timings on steps. It should he obvious which takes longest.
execution plans are helpful once you know which to look at. If it's stored procs or a fairly static query the should be in the query plan.
change the package to manually log individual steps and information that you can clearly analyse later it will help identify the slow running points. Add row counts to see if you can quantify if a per row metric to calculate run time. Even run a query to log current connections and transactions to see if it's clashing with other tasks.
This being said 8 hour sync between staging and live is huge unless we're talking about 100s of millions of rows. The run time could suggest missing indexes on staging but the execution plans should tell you that.