SSIS - Data Flow Task efficiency help...

[Spidy6123]

Hi,
I have a SSIS package which generally runs for about 1.5 hours before it is complete...

Just recently there is one Data Flow Task within the package that runs terribly slow... I don't know why, because it's a very simple SQL that transfers data over from a source server..

I had the server rebooted and performance was back to normal.. but before long.. it happened again.. the same task lags the whole package..

Any thoughts?

 

[mrdenny]

Sounds like you need to do some performance tuning of that query. Is it properly indexed? What does the execution plan say? How long does it run for before it starts to crap out (days)?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[MDXer]

32 bit or 64 bit?
What service pack?
How many records are being transferred?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[RiverGuy]

Make sure that your destination is set to fast load if applicable in your environment.

 

[Spidy6123]

Thanks for your fast responses...

-Both source and destination tables are indexed properly.
-Output window doesn't appear to display anything unusual, t slow rate of transfer.
-I would give it a couple of days before it begins lagging, usually after this package is run for the first time after a reboot. (I should mention the server is used for many other databases - oddly enough, they do not suffer any performance loss)
-Microsoft SQL Server Standard Edition, Microsoft Windows NT 5.2 (3790, NT INTEL X86
-Only about 500k Records (roughly 400mb to 500mb in flat file)

Dont think I can set fast load if it's a SQL query Specific data transfer...

Thanks again guys.. really appreciate it

 

[MDXer]

I would look at the memory available to SSIS after a couple of days. If you are running other databases from this server then possibly after a reboot the resources exist to quickly execute the package, while after the system has been up a while SQL starts taking more of the memory, leaving less for SSIS. 1.5 hours seems like a long time to import 500K records.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Spidy6123]

Thanks I'll look into the memory usage..

The import is just the first step.. there's a bunch of data manipulation afterwards which takes up some time..

 

[MDXer]

can you describe the basic structure of your process sometimes using the wrong task or having tasks in a different order can impact performance.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck