Sqlagent data transfer issue
-
I am not an sql person but have been asked to investigate strange behaviour when transferring large amounts of data from Linux to windows using sqlagent.exe.
wireshark seems to suggest that for every packet sent, an ack is returned and appears to be ignoring tcp window size. Pic below and any advice/pointers would be great.
many thanks in advance.
Mick.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
SQLAgent is a scheduling/control program... not a data transfer program. What is SQLAgent calling to do the transfer. That's what you need to look at.
--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) -
Many thanks for your help. I have been told that it uses DTExec.exe to run the SSISpackage. I am trying to find out more about this but if anyone can advise I would be most grateful.
Mick.
-
So what is the strange behavior - and how is that impacting the data transfer? The process appears to be using SSIS - but knowing just that won't help much.
How is SSIS getting the data - and how is it loading the data? Is the source a file - a database - something else? Is the destination a table in SQL Server? Or - is the source the SQL Server and the destination a file on the Linux system?
If the source is a database on the Linux system - is it using an ODBC driver, an OLEDB driver or ADO.NET or something else? If the source is a file on the Linux system, how is that file accessed across the network (SAMBA?).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Hi, thanks again for your time here…. I will get answers to your questions on Monday as I am not an SQL person…
yes it is using odbc as this command is run on a Linux box at one site that goes and gets the contents of part of a database at another site (windoze) and imports it locally.. never used to be an issue as both servers were on same subnet/site so transfer was local on 10G switch. We are now using SDWan between sites/servers and this of course means some latency in packet transfer… only a few milliseconds but the issue I am seeing is that the devices negotiate a tcp window size which should amount to thousands of bytes of data prior to seeing any ACKs but a packet of 900k is sent and then a PSH-ACK is returned for every single packet. (This is in my original post) If we send similar size file via ftp then we see thousands of bytes forwarded with only one ACK. So if the latency is only a few milliseconds the multiply that by 20 thousand packets and ACKs it soon mounts up…. I am looking into command line to reduce the number of ACKs during the transfer but so far no luck…. I will forward more detailed info as and when I get it but thanks again for your time.
Mick.
- Blokeandbird wrote:
Many thanks for your help. I have been told that it uses DTExec.exe to run the SSISpackage. I am trying to find out more about this but if anyone can advise I would be most grateful.
Mick.
That's correct. The issue is in the SSIS Package... not SQL Agent. The SSIS Package is what is transferring the data. The questions that Jeffrey Williams asks are spot on in getting information that will help folks help you.
--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) -
Ok did some searches on SSIS latency and found this…..
sorry but was unable to paste in iOS. So will be taking a look at pooling option…
Mick.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply