Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
VPN is transparently done by our ISP provider which uses Cisco routers. –  splattne Aug 22 '11 at 9:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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