5

I have a mysterious phenomenon on one remote SQL Server 2005 which I manage and query using SQL Server Management Studio. I connect to the server through a VPN tunnel (provided by an ISP, about 8 MBit/s up and down).

When I open a new query window (first connection) the result is quickly transferred to the client. Let's say < 1 sec. When I repeat the query a few times, the results transfers are getting slower, up to 5 sec and more. It has nothing to do with the query execution time, it's just the transfer time to the client (SSMS).

Now, even stranger: I opened a second query window to the same server/database, the query result transfer is always fast even if I run them at the same time. Even after two hours the results are always transfered very quickly in that other window, while it takes much longer in the first query tab.

I'm using this testing query in order to avoid some potential caching issues:

select top 2000 newid() from <SOMETABLE>

I tried to change the server registration's connection packet size, but it didn't change much.

What could cause the problem?

Update: as suggested in the comments I cross posted on Serverfault.com.

6
  • What if the same query is run repeatedly via sqlcmd?
    – billinkc
    Aug 17, 2011 at 18:51
  • @billinkc hmm, same effect: time between 0.5s to 4s.
    – splattne
    Aug 17, 2011 at 19:13
  • 1
    You might try cross-posting on ServerFault.
    – Richard
    Aug 17, 2011 at 19:21
  • It seams that it is not sql server problem.
    – garik
    Aug 17, 2011 at 20:09
  • what happens when closing the first tab after 2 hours?
    – gbn
    Aug 18, 2011 at 8:08

2 Answers 2

3

In your VPN properties, click options, PPP Settings. Do you have "enable software compression" ticked? SQL data compresses quite nicely, and if the remote site is heavily used bandwidth wise, it may simply be struggling to send you the data.

1
  • VPN is transparently done by our ISP provider which uses Cisco routers.
    – splattne
    Aug 22, 2011 at 9:40
1

For the record: I could resolve the issue. It was a network-related problem.

Our ISP found significant packet loss on VPN connections which affected IPsec decryption. They did some optimization, Cisco router updates and a reboot of the microwave radio relay that we're using.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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