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.
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?
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 :(