Very Computer

We are having a large performance problem with SQL Sever over a WAN.  We
have an application which retrieves small result sets that performs
perfectly fine over the LAN (1 to 2 second response), but lags over all Wan
connections (10 seconds to over a minute for the same results).  We've been
able to determine that it is not a Wan bandwith problem (usage is well
below capacity), nor a WAN configuration issue (all routers are at
defaults, and plain 5Mb file copies between points show no performance
problems at all).  We've also been able to determine that the client is
receiving varying packet sizes fromt the server during these requests (some
packets at 1500 bytes, then some much smaller).

We are wondering if this could be a SQL server issue, as we've eliminated
most everything else.  Is there any way to configure SQL Sever network
communications, beyond choosing protocol.  We are using TCP/IP exclusively.
 Any help will be greatly appreciated.

Thanks,

Greg Hayes

 Not really.  Other than using tcp-ip sockets net-lib there's nothing
much you can do from the config side.

 Q.      How can I speed up SQL Server applications running over slow
links.
(v1.0   02.10.1998)

A.  First we need to define what a "slow" link is.  Typically this is
anything from 64Kbit/sec and down.  On links of this speed the size of
a resultset and the number of network packets that are exchanged can
make a significant difference to overall response times.

First, either do a network trace, or use SQL Trace to see what exactly
is being transferred during a typical client session.  Then try the
following :-

1.  If large intermediate resultsets are being returned, then see if
you can write the logic into a stored-procedure so that only the end
results are returned.  Try and reduce the number of sent/received
pieces of SQL by using stored-procedures as much as possible.

2.  If the connection uses ODBC and the overhead it creates running
sp_serverinfo, sp_cursor, temporary stored-procedures etc. is causing
the problem then use passthrough queries if possible and turn off the
temporary stored-proc creation in the ODBC dsn properties.

3.  Configure the db-lib/ODBC connection to use the tcp-ip sockets
net-lib.  This performs best over slow network connections and can make
a significant difference.

4.  Is the application using client-side cursors?  Try v3 or above of
ODBC which should give you transparent server-side cursors.

5.  Don't return 1000 rows to the client if all they need to see on the
screen is the first 20.

6.  If there are large amounts of static data that need to be retrieved
then consider replication to a client copy of Access, SQL 6.5
Workstation or with SQL 7.0 a local copy of SQL Server.  Over slow
links this should only really be used for mainly static data.

7.  Don't send any SQL across the link at all.  Use Citrix or NT
Terminal Edition to run the application centrally and install ICA/RDP
clients on the remote machines.  The applications then all run locally
on a server next to the SQL Server (the same box isn't recommended).  
The only thing that goes across the slow-link are screen-updates, which
are optimised and compressed and so will often work satisfactorily on a
14.4Kbit/sec modem link.  This also has the advantage that there is no
longer any client code to maintain at the remote sites either.  There
are whitepapers on Citrix, MS and Compaq's sites about sizing the
server(s) you will need to run in this mode.

 Neil Pike MVP/MCSE
 Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated
otherwise)