6

I am working on optimizing a data flow task, I thought I will see which component takes how much time. I got these tasks:

enter image description here

How should I find out how much time each took? Except time if i can find out about other resources like memory, CPU etc. that will be great.

Primary I am looking at time each component took.

stb
  • 3,405
  • 2
  • 17
  • 24
Pritesh
  • 1,938
  • 7
  • 32
  • 46
  • 1
    If your data flow looks much like this + a destination, and your performance is not what is expected, dollars to dumplings your source query is pulling back too much data. Only pull back the needed columns and tune your query in the source system. – billinkc Aug 21 '12 at 15:00
  • @billinkc it's query over Sybase which is slow :( no option of optimizing left on that side. – Pritesh Aug 21 '12 at 16:14

2 Answers2

4

For quick review of the elapsed time see the Execution Results Tab. It will display the Elapsed Time when a task/component starts and finished.

Also

Monitoring Performance of the Data Flow Engine This is a little more involved and it slows down my PC when doing it. YMMV.

Also SSIS contains a pretty good logging mechanism for errors, information and a myriad of other events if you need to troubleshoot something:

From your menu SELECT SSIS, Logging...

You'll need to specify a provider. To keep it simple until you get the hang of it I'd suggest SSIS log provider for Text Files. It's CSV format and you can customize the information returned.

To monitor all of the components in the package: Click the Details tab and select OnInformation and OnPipelineRowsSent as suggested by billinkc. Be mindful that Logging is useful for troubleshooting, but OnPipelineRowsSent is verbose so you might want to remove that event from the log when you deploy to production to reduce write times and log sizes.

SSIS Log - For entire package

To monitor only a specific task: Uncheck the package and then check the data flow task/component from the package tree view that you would want to monitor. After you select it then choose the events you want logged start with OnInformation (and OnPipelineRowsSent as suggested by billinkc).

SSIS Log - for specific component

Charles Byrne
  • 834
  • 2
  • 11
  • 20
  • Just to clarify, when an item is grayed out as above, that indicates the parent container's logging details will be inherited. Thus, if Import CG1 has OnError and OnExecStatusChanged selected, PD Stub will log those events but the LoadCG1 Flat F will **only** log OnInformation events. When you click it as you've done for `Load CG1 Flat F` will overwrite the captured events. I generally find it sufficient to set logging at the root level. – billinkc Aug 21 '12 at 14:57
  • It was a bad screen shot on my part. I was logging OnError and OnWarning events at the package level. I'll edit. – Charles Byrne Aug 21 '12 at 15:32
2

If I want to determine timings between components on a data flow, I use the native logging and capture the OnPipelineRowsSent event. This indicates how many rows have moved between transformations. James has a good query and writeup on it over on sqlteam

One thing to note, you cannot log OnPipelineRowsSent event until a data flow exists in a package. I always felt that was a shame from the standpoint of creating template packages.

billinkc
  • 59,250
  • 9
  • 102
  • 159