Long running data flow task

Question

Long running data flow task

karen.ferrara

Right there with Babe

Points: 770
More actions
December 29, 2016 at 7:34 am

#333042

Hoping somebody can help. We have a data flow task in an SSIS package that is called from a SQL job. The task takes data from a view on one server (view takes less thank a second to run from my local machine) to a table on another server. This task is part of a package that usually takes 2 to three minutes to run. Twice now the package has run for hours, getting hung on this data flow task. What could possibly be causing this? There is no deadlock, no process that this process is waiting for, etc. Any help is appreciated. This is production so I cannot run the task locally. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

Possibly out of date stats on the source? Maybe parameter sniffing? Maybe someone had a long winded transaction on one of the underlying tables of the view?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tim Mitchell SSCoach Points: 15681 More actions · Answer 2

This is a case where the execution logs will be very useful. Are you executing from the SSIS catalog? If so, take a peek at the logs for that execution.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Re-reading the original post, I'd also check and see what was going on in the form of blocking during those times on the remote server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karen.ferrara Right there with Babe Points: 770 More actions · Answer 4

What we found is that one of the underlying tables used in one of the views the query uses had 20 million + rows. Rebuilding the indexes prior to running the query caused the error to go away.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

karen.ferrara (1/4/2017)
What we found is that one of the underlying tables used in one of the views the query uses had 20 million + rows. Rebuilding the indexes prior to running the query caused the error to go away.

Don't forget that Rebuilding indexes causes statistics to update. There's a real high probability that updating statistics on the table would have done it without the ardor of rebuilding indexes on a large table. Try rebuilding stats first the next time and see what happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karen.ferrara Right there with Babe Points: 770 More actions · Answer 6

karen.ferrara

Right there with Babe

Points: 770

January 4, 2017 at 8:59 am

#1920665

Thanks for the advice!

MMartin1 One Orange Chip Points: 27764 More actions · Answer 7

If you are using a OLE DB destination adapter in the data flow then you can also tinker with the "Rows per patch:" or the "Maximum insert commit size:" options to see if you can get a little less overall run time if you are moving tons of data across the network.

----------------------------------------------------