mnation1
asked on
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!
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!
ASKER
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.
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)
ASKER
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.
(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.
ASKER
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:
[Application]
Path="driver={SQL Server};Provider=SQLOLEDB.
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.
The one thing I CAN control on the client is the .ini file where I can specify an application path:
[Application]
Path="driver={SQL Server};Provider=SQLOLEDB.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
ASKER
@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.
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.
ASKER
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
and
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.
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
and
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
the thousands of individual queries.
Was statistics type entries also suggested? These help in the formulation of the execution plan.
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
the thousands of individual queries.
Was statistics type entries also suggested? These help in the formulation of the execution plan.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
http://sqlblog.com/blogs/john_paul_cook/archive/2008/04/13/simulating-high-latency-and-low-bandwidth-for-database-connectivity-testing.aspx
http://sqlblog.com/blogs/john_paul_cook/archive/2008/04/13/simulating-high-latency-and-low-bandwidth-for-database-connectivity-testing.aspx
ASKER
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.
@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.
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.