How to track SSIS memory and CPU performance?

Question

I may be missing some pieces to this puzzle, but this is what I have:

All of this was written for us by an outside firm.

There is a .Net application that runs as a service on Server A.

Inside that application SSIS packages are being called. DTEXEC?

Those packages (37 total) reach out to two different SQL Servers (B&C), pull in some data, perform calculations on it, then insert the results into a database on Server C.

Of the 37 packages, only a few of them are my problem area. I've seen the details, and there are many, many tasks in them.

There are no SQL Server components (database engine or SSIS) on Server A where the .Net app lives, so this is all churning internal to the application. .Net is using 1 NUMA node of 20 cores, 1TB RAM on the box.

This app is 3.0 version, with new functionality added. 2.4 (previous version) works fine on identical hardware.

All of that background to ask this:

Is there any way I can find poorly performing T-SQL inside the SSIS packages that are inside the .Net application? Or am I missing something hugely obvious in the equation?
What it boils down to is: "where are the calculations/T-SQL actually running" (A vs B/C) and how do I trace/tune them if inside the .Net space?

Profiler, sp_whoisactive, etc. all point to SQL instances. This is all happening solely inside the .Net app, with small exceptions to select data from B and insert to C after calculations. All network, disk, etc. is performing perfectly. I'm looking for a tuning tool that can reach into the .Net SSIS if such a thing exists.

It's definitely a tuning issue, but Profiler, sp_whoisactive, etc. show nothing of more than maybe 3 seconds, most well under 1/2 second and less. I don't know enough about .Net to know if those T-SQL tasks are actually running on server A inside that memory space like I think they are, or if there are just so many of them that its all aggregating into "too long".

I wish I was allowed to enable heavy logging in SSIS, but this is part of the vendor code I am not allowed to touch, even for logging :(

Answer 1

The environment that this question was directed at has long since been torn down and re-purposed several times.

The problem popped up again on the current UAT iteration...and we found that someone had pointed part of the application to the 32-bit version of dtexec.exe in Program Files(x86), instead of the 64 bit correct one.

oops. Lots of research and this DBA.SE post got me there.

Messages in my logs that I didn't have the first time around:

Not enough Storage is available to complete this operation
A buffer failed while allocating x bytes
The attempt to add a row to the Data Flow task buffer failed

There are also some SSIS specific perfmon counters that helped:

I detailed a lot of my steps here: http://dallasdbas.com/ssis-memory-errors/

Hope this helps someone else down the road!

Answer 2

Have you looked into RedGate's reflector? It sounds like exactly what you're looking.

Note: I have no affiliation with RedGate. Web searches solve problems!