sql server and vpn: performance/speed
Bookmark this page:
 Yahoo!
 Google
 Windows Live
 del.icio.us
 digg
 Netscape
|
|
||||||||||||||||||||||
|
Posted by JENS CONSER on June 1, 2005, 7:27 am
Please log in for more thread options Hello NG, We have a performance problem in using a client server solution based on MS SQL-Server 2000 through a VPN tunnel (via broadband internet connection). The SQL Server is running on a Windows 2003 Server which is configured as VPN server as well. We figured out that not the performance of the VPN tunnel itself is the problem, but the problem is the access to the MS SQL Server. We tested it by executing sql statements on the Query Analyzer on the VPN client (WIN XP built in) which is connected to the server through the tunnel. For example execution of the SELECT statement "select TOP 1000 * from Items" on the database trough the tunnel takes more than 30 times as much than the same execution on the server itself or within the server's local network. But we can make FTP downloads from our server through the tunnel with 600 KBit/s under same conditions so the bandwidth of the tunnel should not be the problem. Do you have any ideas how we can accelerate this or does any one has made experiences related to this topic? Thanks for your help! JENS | ||||||||||||||||||||||
|
Posted by Robert Klemme on June 1, 2005, 4:38 pm
Please log in for more thread options JENS CONSER wrote: I don't think you can easily compare download speed (simple unidirectional) with JDBC server communications (several calls back and forth). Network latency is probably killing you. This latency might stem from WAN and / or from encryption overhead. You can try this - increase fetch size for query results - choose another select method (cursor vs. direct) - tweak network settings to change the size of packets transferred IMHO any DB protocol is sub optimal for wide area deployment. I'd rather use a protocol tailored to your application's needs between client and server, which also means you need another component on the server side that is a client to the db and a server to your clients (middle tier). Kind regards robert | ||||||||||||||||||||||
|
Posted by hals_left on June 1, 2005, 6:32 pm
Please log in for more thread options
I dont know much about VPN (yet) but I know that SELECT * causes an extra trip to the database to fetch the fieldnames. Are enterprise manager tools required a lot in the live environment? if so you could look at the SQL Server Web Data Administrator, but would need to deploy IIS/.NET. http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en | ||||||||||||||||||||||
|
Posted by Erland Sommarskog on June 2, 2005, 12:58 am
Please log in for more thread options
JENS CONSER (jens@e-sistrum.com) writes: > We tested it by executing sql statements on the Query Analyzer
> on the VPN client (WIN XP built in) which is connected to the server > through the tunnel. > > For example execution of the SELECT statement > "select TOP 1000 * from Items" on the > database trough the tunnel takes more than 30 times as much > than the same execution on the server itself or within the > server's local network. > > But we can make FTP downloads from our server through > the tunnel with 600 KBit/s under same conditions > so the bandwidth of the tunnel should not be the problem. When you run QA directly on the SQL Server Box, or in your local network, you have something like 100 MBit/s. 600 KBit/s is far more than 30 times slower. What speed is your VPN connection labeled at? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp | ||||||||||||||||||||||
|
Posted by JENS CONSER on June 2, 2005, 5:02 am
Please log in for more thread options
we did the ftp-test only to proof that the tunnel speed is not the reason and to see the current performance of the tunnel. the vpn connection is shown on the network monitor on the server with 28kbit/s and on the client with 100mbit/s but both has nothing to do with the real performance. especially the server speed is a real fake. and also in a lan the speed is not real 100 mbit/s despite it is shown for the network adapter. so approx. 600 kbit/s should be the maximum performance of the tunnel from our client (in egypt) to the server (in usa) when i check the usage of the tunnel i see e.g. for this select statement a data transfer of around 15 kbit/s. so the tunnel bandwith should not be the reason. when i executed the sql statement it takes 5 seconds on the lan and more than 3 minutes through the tunnel. i tend more to roberts opinion that the network latency is the reason and the bidirectional communication, although i can't image that a simple select causes so much communication. or does the server send each row seperately and waits for the response :) ? by the way ... i only used the query analyzer for testing, as i wrote we have the real problem with our client-server-application. i think i will try first to increase the packet size like robert said, does anyone know how to do it in windows, before i waste time to serach for it? i have to do it on the client and on the server as well, right? robert, is the increasing of the fetch size something global (which i would need) or only a option in the query analyzer. thanks for your help JENS | ||||||||||||||||||||||
| Similar Threads | Posted |
| Title: VPN server in Windows 2003 server web edition getting random restart | February 11, 2006, 2:38 am |
| vpn server | February 25, 2006, 1:08 pm |
| vpn for windows 2003 server? | April 11, 2005, 4:29 pm |
| Nortel Contivity to MS ISA VPN server | May 9, 2005, 6:30 pm |
| sql server and vpn: performance/speed | June 1, 2005, 7:27 am |
| pam capable vpn server 4 linux | June 30, 2005, 1:39 pm |
| Setting up proxy server on Mac | August 21, 2005, 7:48 pm |
| VPN server for use with native XP client? | September 7, 2005, 12:38 pm |
| Creare un server vpn su Windows 98 | October 5, 2005, 9:27 am |
| Server message Block | January 20, 2006, 1:38 pm |
| how to remote access a server via vpn? | May 28, 2006, 12:23 pm |
| XP VPN server behind Linksys error 721 | August 29, 2006, 9:33 pm |
| SMTP Server unreachable through VPN | October 11, 2007, 3:25 pm |
| 3-site VPN implementation w/Terminal Server | August 17, 2005, 3:32 pm |
| How Secure Is The Built-In VPN Client/Server in XP? | December 29, 2005, 3:45 am |
>
> We have a performance problem in using a client
> server solution based on MS SQL-Server 2000 through
> a VPN tunnel (via broadband internet connection).
>
> The SQL Server is running on a Windows 2003 Server
> which is configured as VPN server as well.
>
> We figured out that not the performance
> of the VPN tunnel itself is the problem,
> but the problem is the access to the MS SQL Server.
>
> We tested it by executing sql statements on the Query Analyzer
> on the VPN client (WIN XP built in) which is connected to the server
> through the tunnel.
>
> For example execution of the SELECT statement
> "select TOP 1000 * from Items" on the
> database trough the tunnel takes more than 30 times as much
> than the same execution on the server itself or within the
> server's local network.
>
> But we can make FTP downloads from our server through
> the tunnel with 600 KBit/s under same conditions
> so the bandwidth of the tunnel should not be the problem.
>
> Do you have any ideas how we can accelerate this
> or does any one has made experiences
> related to this topic?
>
> Thanks for your help!
>
> JENS