1

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.

  • Note that you'll be able to tell where the job is spending its time today - but unless you already have such information from (say) two weeks ago, you won't be able to tell where there's a difference in how its spending its time. Also, don't neglect standard troubleshooting: any new (or updated) jobs start running two weeks ago or so? Any change in the amount of data to be processed? – RDFozz Jul 20 '17 at 17:42
  • I totally get that and there are some days when the data load is not that heavy and it completes quickly whereas the same is not true for other days and it takes longer to complete. However, I do need a way to figure out where exactly in the process it spends a lot of time waiting on that the run duration increases so drastically. – Feivel Jul 20 '17 at 19:06
  • 1
    Take a look at this question and answer. While not really a direct duplicate of your question, I think it provides an answer for you - using the logging capabilities of SSIS. Since you're using 2008, you'd be doing it "the hard way", but you should be able to track when you enter and leave certain states for each task in your package. – RDFozz Jul 20 '17 at 19:15
  • 8 hours is a longtime for any integration or etl job. How many rows/gb are we talking about. How much longer is it now taking? – Sir Swears-a-lot Jul 21 '17 at 10:25
  • Yup...8 hrs. is long by any means. Currently, it is close to 100,000 records per hour. – Feivel Jul 21 '17 at 19:41
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.