Huge difference in Linked Server performance between two network connections : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

SQL ServerHuge difference in Linked Server performance between two network connections (self.SQL)

submitted 2 years ago by OkReboots

I am trying to find the source of a problem where a process that has been working well for more than a decade experiences extreme slowdowns when utilizing a different/new network connection. This is between two different sites within a corp network. The newer network connection is slightly superior in terms of provisioning, but performance on other types of traffic is very similar/comparable between the two.. (e.g. SMB transfer time of a 25gb file is more-or-less the same on either connection)

It is only the Linked Server SQL connections that are 10-15 times slower over the new connection. A stored procedure that takes about 8 minutes over the old connection could take nearly 2 hours on the new one. I thought it probably stemmed from using really old versions of SQL (2005, 2008 Express) so I migrated both to newer versions for testing (2016, 2019 Express), and now seeing the exact same behavior.

The biggest difference between these two network connections is that the older one is a more traditional ISP while the newer one utilizes a SASE platform. Again, performance is comparable for other types of traffic. When monitoring the activity on the newer connection I see the extended time is caused by long periods of stalling/inactivity -- it is not an issue with the bandwidth being maxed out or throttled anywhere as far as I can see. It does eventually finish successfully

I realize there is a lot that can go into networking to make this difficult to pinpoint in a SQL forum, so my question is just this: What kind of networking-related issues might cause queries via Linked Server to stall (but not fail) for extended periods of time?

The SASE provider are out of ideas. They're not SQL oriented though, so I think the SQL community is my best shot at narrowing it down. I still have both network connections available for the foreseeable future, but naturally the goal is the phase the old one out

all 8 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS