SQL apps slow over VPN

Hi,  I have a 22down/5up cable connection at two sites and have a BOVPN between the two (both Watchguard XTM 505, 11.4).  Everything is running swimmingly with windows file transfers, internet, Outlook and other applications getting expected speed - approx 5Mb/s.  

However, we have two VB apps that pull data from our SQL server at one of the sites, and when they are accessed from the other site through the VPN they take an extremely long time to start up, and an annoying amount of time accessing different records once they are open.

Previous to this set up we had a 1Mb/s P2P connection between the two sites, and that was also annoyingly slow, but it was two times faster than what we are now getting through the VPN. I 'm probably getting a little more latency from this connection (20-30ms) than our previous one (15-20ms).

I know the encryption will add a little overhead, but other traffic is working just fine and the VPN is rated to 250Mb/s.  

Can anyone provide some insight as to what might be causing the slowness for these apps or any troubleshooting I can try to track it down?

I've already tried changing the SQL connection string at the remote site to the IP of the SQL server instead of the NETBIOS name and that had no effect.  

Thanks very much!
Who is Participating?
FemSteenkampConnect With a Mentor Commented:
the BIG problem is the latency.  if you run a packet trace you will see thousands of packets between sql and client.

on a lan the latency ia maybe 1-4 ms, so the addiitonal latency that the 5 MB Link  incur is for each round trip, and unfortuanltly a lot of htese are sequential, ie SQL waits for confirmation about previous paket before sending next packet.  

instead of Terminal services, you can look at hardware accelarotors like riverbed steelhead appliances


and no i am not working for riverbed, but have implemented their technology over low bandwith  high latency lines to increase performance

yes it will cost money, but the upside is that other traffice like fileshares etc should also be significantly faster between teh sites.
The issue is with the number of queries that the applications sends, receives data, generates the next query, etc.

See whether you have an option to prioritize SQL traffic over VPN. See whether you can increase the allocation of bandwidth/priority to the VPN.
using Quality of Service options.
mnation1Author Commented:
Thanks.  I'll definitely try that that and check back in.  Our traffic isn't coming close to 5Mb/s, and the time the apps take to load is the same whether we have all 20 users active or none (after hours).  Any other thoughts would be appreciated.
We Need Your Input!

WatchGuard is currently running a beta program for our new macOS Host Sensor for our Threat Detection and Response service. We're looking for more macOS users to help provide insight and feedback to help us make the product even better. Please sign up for our beta program today!

what type of encryption are you using, if you are using 3des you could save some cpu cycles by switching over to aes (if that is an option with your equip)
mnation1Author Commented:
Thanks for the suggestion.  Using 256bit AES with SHA1.
When the application starts, it has to access the database and build the various initial parameters?
(The slow down is not following a user's login.)
i.e. list of users, etc. Could the initialization process when the application is launched be converted into a single stored procedure
exec proc_initial_setup
This will depending on the complexity will be a hit on the sql server.
Shifting as many repeatitive/preditable query sequences into stored procedures/functions may offload the load from the network to the sql server.
mnation1Author Commented:
I should admit, my SQL and VB knowledge is not good.  These apps are old and compiled.  Do you think it would be possible to convert to the stored procedure on the SQL end without having to modify the client apps?

The one thing I CAN control on the client is the .ini file where I can specify an application path:

Path="driver={SQL Server};Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=[databasename];Data Source=[sqlserverIP]"

My most recent shot in the dark thought is that I could set up an ODBC connection to the server (TCP/IP) and possibly set the above path in the .ini file to use the ODBC instead.  This thought came from the idea that if the apps were somehow coded to use named pipes, maybe this would allow me to use TCP/IP instead (my ignorance is showing through I'm sure).  I've read that named pipes are much slower than TCP/IP - especially over a VPN.  Does that sound at all promising?

By the way, our SQL server was upgraded 6 months ago from 2000 std to 2008 r2 std and all we did to make these apps work is to update that application path with the new server IP, so I'm not sure if there are enhacements/optimizations I can make with the upgraded SQL.
arnoldConnect With a Mentor Commented:
You're using integrated security=SSPI meaning that the credentials of the logged in user are passed and access to the database is granted based on the user.
Presumably, the database compatibility is set at 80 (SQL 2000)  versus 100 (SQL 2008).

Modification to the client would be required to alter the
select columns from table
exec initialize_me
Which as a response the application will get in one response what it does over the initial queries it generates.

Do you have the SQL 2000 config utility which you can use to force the client connection over to TCP/IP.
I would think that the developers of the application setup a TCP/IP connection.

You could use the proiler/tunning agent to see whether their is a way to optimize the response to the queries by adding indexes, statistics etc. to improve the execution plan of the queries.
mnation1Author Commented:
Thanks, arnold.  The SQL 2000 config utility and profiler/tunneling (sp?) agent are greek to me so I'll be learning about these immediately.  If you have any other thoughts in the meantime please let me know.
SQL is not very good at low latency, low bandwidth connections just because of teh way that the microsoft SQL data transfers happen.

although tweaking the thruput will undoubtably help, you are not going to change the underlying sql transfer (that  does a LOT of small data teransfers).

have you considered hosting the ap on a Terminal Server and have the remote users access it on the TS server at teh main office.   Ts sessions is very lightweigt for the link and all SQL traffic is kept at the main office.
mnation1Author Commented:
I'm coming to learn that about SQL's trouble with latency and low bandwidth, I was just surprised by the performance hit moving from a 1 Mb/s open connection to a 5 Mb/s VPN connection (the latency isn't a huge difference, and the bandwidth is considerably higher.  I've definitely considered Terminal Server as a last resort.
mnation1Author Commented:
@arnold  Can you tell me if I need to run the SQL config utility on the workstation, SQL server, or both?
On the workstations to force the client over TCP/IP.
On the server, you should check the MSSQL 2008 configuration tools to make sure it has the TCP/IP protocol enabled for the server which I suspect it has since you are able to remotely connect.
The issue in the slow down might also be that the application was developed with sql 2000 in mind but you had the same issues prior to the upgrade.
mnation1Author Commented:
I played with the SQL client config utility, and made sure to force the client to connect using TCP/IP, and there was no change in the speed.

I also ran a SQL trace using the tuning template isolating the startup process of each app.  That was a real eye-opener:

For one of our apps there were 7000+ rows in the trace.  For the other there's 4700+ rows.  In both trace logs the vast majority of the events are

exec sp_cursorfetch 180150003,32,1,1
FETCH API_CURSOR0000000000000002

There are literally thousands of these events, and none of them take any time for SQL to process, but I'd guess they contribute quite a bit to the slowness.

The SQL tuning advisor said we could get 7% efficiency out of one app, and 23% out of the other by indexing a few tables.  I'm unsure whether this would have any significant impact on the speed through the VPN (I'm leaning towards no).  I also don't know if creating those indexes could possibly break the apps we have.

If you have any thoughts given the new information please let me know (especially if you have an idea how to get rid of those pesky cursor fetches!).  I'm waiting until the weekend to try some things with the MTU size between firewalls, and the QoS suggestion.

Thanks for your continued interest.
What is the index that it is suggesting?  Presumably it will merely add an index on a regularly queried column as long as it is not an exclusionary/unique index, you should be fine.
Do you have an option to try this in a test environment? where you can test the addition of the indexes and whether it impacts the application?

index that use exclusionary rules i.e. require unique, no duplicates, etc. could have an impact during inserts/updates if the application does not check and tries to insert/update a record where one exists or with the change will match an existing one.

The slowness is not less to do with the performance/responsiveness of the SQL versus
the thousands of individual queries.

Was statistics type entries also suggested?  These help in the formulation of the execution plan.

mnation1Author Commented:
It appears it would only be creating an index to columns.  I plan to make a copy of the database to test this.  

If by "statistics" entries, you mean "Performance Statistics" (an optional event to trace when creating a trace template) then no.  I'll see if I can put together a new template that includes that and also some educated guesses about other events that might be useful and run the tuning advisor on that.

I'll post the results when possible.
to see if the latency is the only issue, check out this link that ahs software that can simulate latency so you can test the VB apps

mnation1Author Commented:
I think it really is boiling down to the latency.  I ran new SQL traces with maximum detail, applied all the tuning advisor recommendations, and still have the same speed issues.  I also worked with our firewall vendor to rule out any misconfigurations in the VPN such as MTU size and encryption modes.  I switched one of our sites back to the P2P T1 and confirmed that the latency is about 6 times less on average than through the VPN, and that correlates well with the time difference it takes to open the SQL apps from each corresponding location.  As a last resort I'm having our ISP look at the latency to see if there's any better they can do.

@Fern - I tried to install the VE desktop client, and it hung, but thanks for the suggestion.  I'll also take a look at the hardware accelerator you suggested.

Thanks for everyone's help.  Especially arnold who taught me a lot about SQL optimization.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.