Performance Finger pointing: suggestions on next steps.

Question

Post reply

Performance Finger pointing: suggestions on next steps.

Lowell

SSC Guru

Points: 323518
More actions
December 20, 2016 at 9:29 am

#316172

I've got a package that does ETL from a server over VPN to something local;
If i run the package on the same server that the SQL2016 destination database exists, it runs quickly...eleven minutes.
if i run it from a different server which is really just our dedicated SSIS execution Server, it takes hours.
the performance problem just cannot be on the SQL server itself. same package, on my desktop, another developers desktop, the SQL server, runs in eleven minutes.
from one specific server, ugh...hours and hours.
so far, i've only identified the issue occurring on ONE SPECIFIC package, but always to the same remote VPN destination, but it could be potentially all packages hitting a VPN
So my knee jerk reaction is that this list so far:
bad SQL driver on that one server.
some networking thing: bad nic card maybe, some sort of throttling from that server.
so as a SQL guy, how do i check if a server is having bad nic cards, slow connections., net working throttling/bad providers?
I know i can go the lazy route and just reinstall the latest SQL2016 drivers again, but the other items have me looking for network throughput monitoring tools, so i can see if things are slowing down or retrying or whatever.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

To identify what specific task or event is the bottleneck, you can look at the SSIS Executions report.

\'Integration Services Catalogs'

\SSISDB

\ProjectName (typically name of your package)

Right click on project

Reports..

All Executions..

Also, the performance of tasks like Lookups or SCD can significantly degrade depending on whether you run them from remote server or locally on same server hosting source database.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Lowell SSC Guru Points: 323518 More actions · Answer 2

right, that of course was one of the places i've been looking, as well as the classic slowest running queries.

this package is extremely simple, it's pure truncate and reload, no derived columns, no special lookups or anything.

the report doesn't show errors, it just shows a huge time to execute;

OnPostExecute = "Process Acct Tables: Finished 11:26:39AM Elapsed Time: 04:02:35.906."

so the report says effectively "yep it took a long time, but it doesn't have anything that really shows what was the delay.

that's where i'm thinking i need something watching the network isntead maybe?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

Apps that pull data across VPN, like SSMS or SSIS, can be impacted by network latency.

One thing you can do is poll SYS.DM_OS_WAIT_STATS for network related wait states during the time you're running this ETL process, comparing cumulative wait stats when running locally on source database server versus remotely over VPN.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

If the same SSIS package runs fine everyone except on one server, it's likely a hardware problem or a hardware setting problem. The first thing I'd check for is are the related NICs, switches, and routers setup for "auto-negotiate". If they are, that needs to be changed to a fixed value. Also, if any of them are set to "half-duplex", they need to be changed to "full-duplex". If all that's correct, the you have to look at the actual hardware itself. Bad NIC, bad channel on Switch or Router, and even bad cables (and, yes, they can go bad over time... example... bad crimp allowed a contact to back out just enough to be intermittent) have all been problems that I've seen in the last 5 years on more than 1 occasion. I've even seen where a CAT 5 cable that has a hard bend in it cause such problems. I've even seen it where a coffee pot or fluorescent light or fan plugged into the same circuit as one of the switches cause enough line noise to cause countless retries.

Another possibility is that the box has virus checking on it that hasn't been taught to ignore SQL Server related directories and files or the CPU's have been allowed to go into a power save mode, which doesn't actually provide full power even when a full power demand exists.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

One thing that comes to mind is to test a simple transfer from Source to a file on the SSIS server, and compare that to the same from your desktop.

Then test transferring that data to the destination SQL both from SSIS server and desktop.

This may isolate the particular system that is slow - VPN, local network or SSIS

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);