5

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

  • 2
    Are you certain they are using SSIS? Could the packages be installed on B, C or perhaps unlisted server D? The reason I ask is you state there are no SQL Server components on A which means you can't run an SSIS Package there - either via command line tools like dtexec or via .NET object model. If the third party has managed to install all the "required" SSIS DLLs to run an SSIS package, congratulations, you now have another server to pay for SQL Server licensing on. Otherwise, they are making a remote call to a licensed server to run their packages – billinkc Jan 26 '17 at 15:36
1

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!

  • never even heard of it...will that help me see the performance of the various SQL Tasks in the SSIS packages that are running via dtexec? – Kevin3NF Jan 26 '17 at 12:35
  • If you click on the link it will show you what it does. It will help you examine the .NET code that you believe to be at fault. – SQL_Underworld Jan 26 '17 at 17:08
  • SQLSentry also has something simular... received a blog-post on it. Red Flag Radar. Looked nice. Note: Not working for any of the monitoring companies ... Just a consumer. – Harry Leboeuf Apr 13 '17 at 13:12
0

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: enter image description here

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

Hope this helps someone else down the road!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.