SQL client/server communications over a WAN

[BOK]

We have had slow performance to all three of our regional offices on this SQL application (we'll call it "AVGPRICE"). This problem started since the introduction of this application a few years ago. And we've basically learned to deal with it. Since then, we've had increases in our bandwidth (128K/256K frame relay, up to 320K/640K ATM). At this point, we don't think it's a problem with our network, since so much as changed, and the slow performance with AVGPRICE hasn't. Are there any special adjustments that need to be made (on the SQL server side, or the database/query side) to accomodate for communications over a slow(er) WAN link? Any input would be appreciated.

 

[ScottMac]

Some additional information is necessary:

What is the hardware platform for the SQL server? Processor, RAM, and type/speed of the NIC.....

What routing protocol (RIP, EIGRP, IGRP...) are you using across the WAN, if any?

How many clients are accessing the server across the WAN, and approximately how often (per client)?

What are the endpoints of the WAN (router? What kind? Model?)

What is the architecture of the LANs on either side of the WAN? Hubs? Switches?

Was the client application "canned"/off the shelf? or Custom written?

Was the server application "canned" or custom? Who set up the database structure?

Do you know the approximate size of the average information transfer (per request)?

Is there any other application or resource being accessed through the WAN by the remote branch (like email, Internet...)?

There's some other questions, but these'll get the ball rollling.

Depending on the size and nature of the information traversing the WAN, Frame-Relay may have been a better choice. With ATM, the data is broken into 48 Byte chunks with a 5 Byte header per chunk (~ 10% overhead). Frame-Relay can take the full-size Ethernet frame. If the information being sent is larger frame sizes, the overhead is reduced.

ATM is a wunnerful thing, I love it, I really do. If you were also doing some Voice or real-time video through the WAN link, it is your best choice. For big chunks of raw data, standard Frame Relay would be a better choice.

You may be able to tune your MTU size for better utilization (less overhead), eliminate or change your routing protocol to something less "chatty,"or make some adjustments on the server or either/both LANs.

Depending on the size, scope, and nature of the data, you may want to set up a secondary SQL server on the remote site. This option may or may not be viable, depending on how current the data must be. If your proocessed data can be up to 24 hours old without affecting its value, then pull the remote data from an on-site server and update it overnight (or during slack time) through the WAN.

Let us know. You're likely to get some good responses on this......


FWIW

Scott

 

[nealr]

If it's fast on the local network, then you've probably got, either way too many sql statements, or maybe it's a com application that's slow, or something else going on.
I usually try to write efficeint sql statements, and move the data to an array to play with it, so the connection is closed.

 

[BOK]

ScottMac. . .

First let me inform you that I am by no means a SQL-guy, so bear with me. I'm the network administrator here. Here's the answers to your questions:



<< What is the hardware platform for the SQL server? Processor, RAM, and type/speed of the NIC..... >>



Dell PowerEdge 2550 - Dual PIII 933, 1024MB RAM, Intel Pro Server NIC set to 100mb/full



<< What routing protocol (RIP, EIGRP, IGRP...) are you using across the WAN, if any? >>



EIGRP



<< How many clients are accessing the server across the WAN, and approximately how often (per client)? >>



Three clients each on two of the regional offices (San Fransisco and Chicago), and 5 clients in the Boston office. Server is located at headquarters in Connecticut. The application is used pretty much continuously throughout the trading day 9:30am - 4:00pm. Also, virtually no improvement in performance (still slow) after hours. This is another reason why we don't think it's a networking/bandwidth issue.



<< What are the endpoints of the WAN (router? What kind? Model?) >>



Dual Cisco 3640 routers (one connected to each of the two redundant ATM networks) at each location.



<< What is the architecture of the LANs on either side of the WAN? Hubs? Switches? >>



Cisco Catalyst 6509 switch at Greenwich, dual MSFCs. Ethernet 0/0 on the 3640 connects straight into a port on the switch. On all the regional offices (SF, Chicago, Boston), it's a Cisco Catalyst 3524 switch, to a Cisco 1751 router, to the two Cisco 3640 routers.



<< Was the client application "canned"/off the shelf? or Custom written? >>



Custom written.



<< Was the server application "canned" or custom? Who set up the database structure? >>



No application on the server-side other than SQL Server 2000 (problem was there even when the server was running SQL Server 7.0). Database structure was created by our programming team.



<< Do you know the approximate size of the average information transfer (per request)? >>



How can I find this out? We have a sniffer that's capturing everything going to and from the server. We can see the queries and the RPCs.



<< Is there any other application or resource being accessed through the WAN by the remote branch (like email, Internet...)? >>



Yes. Every other application is lightning-fast since we implemented this new ATM network a few weeks ago. Internet traffic goes to our Connecticut office as well.

 

[BOK]

Some more information:

All of our resources are here in Connecticut.

AVGPRICE performs well for our 75-100 users here.

TCP/IP is the only protocol on our network.

The server barely breaks a sweat - CPU% is ~ 25%, virtually no paging going on, disk I/O is fine.

 

[Santa]

What is the Query pulling up? How much data is transversing back and forth when a request is made? Check into your software and database creators because it sounds like it could be a poorly written client/server application or a bandwidth intensive one.

By the sound of a couple of your posts you have no idea what type of utilization you have over the WAN. I would suggest looking around for some ATM WAN reporting tools to check out how heavy the load is on the links.

I am not familiar with ATM but we are Frame-Relay and are looking into a side administrative package from SolarWinds.net on top of using around Visual Uptime suite.

 

[err]

we are also having this problem on our company where sql db is damn slow over the WAN, and even on internal LAN.

Our problem was due to hardware. Our quad 450 Xeon db server is just not cutting it. Queries takes so long and it would sometime times out with remote sites.

Our new 16 way xeon 550 server helped improved performance by much. What used to be a several hours reports query take minutes now.

On the WAN link, for some reason there were proxy clients on irrelevant clients machine that doesn't need that and that only worsen performance. As for routing wise, eigrp is working fine for us.

Try looking for other response time and make sure that not only SQL is slow. If everything is slow, look at name resolution and other network problems.

well, it is a b!tch, but keep troubleshooting and take off unlikely cases

Good luck

eRr