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.

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

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.

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

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, privacy policy and cookie policy

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