SQL server bottleneck

22 Sep 2006 8:11 PM

jhall

I have a customer that is operating over a WAN on a T1 line. They are
experiencing unsatisfactory performance. (they have tried using ODBC
and OLE-DB connection) They gave the application 30% of the bandwidth a
put a monitor on it. It never used more than 10% of the available
bandwidth. They switched to an Access db with the same program doing
the same queries and saw the bandwidth usage bump up to near 100% for
short periods and they had satisfactory performance.

I am using VB6 with ADO.

Using the SQL Server from a local network connection gives satisfactory
performance.

The amount of data transferred is really quite low, so the slow
performance is a mystery.

What could be creating this bottleneck in SQL Server?

23 Sep 2006 2:36 PM

Jeje

you have to identify the bottleneck by monitoring the SQL Server, the
bandwidth and the client.
how th VB6 aaplication is writen? do you do some connection pooling?
there is an overhead if you close and reopen a connection at each server
call.
also you can change the network packet size option of your connectionstring.

if locally there is no issue, then SQL Server is probably not the cause of
the issue.

do you execute SQL statement which return large resultsets or do you call
SP?

have you plan to use the synchronization feature?
maybe you can setup a local server and synchronize it to your central
database

<jh***@datalyzer.com> wrote in message
Show quoteHide quote

news:1158955884.189983.128910@m73g2000cwd.googlegroups.com...
>I have a customer that is operating over a WAN on a T1 line. They are
> experiencing unsatisfactory performance. (they have tried using ODBC
> and OLE-DB connection) They gave the application 30% of the bandwidth a
> put a monitor on it. It never used more than 10% of the available
> bandwidth. They switched to an Access db with the same program doing
> the same queries and saw the bandwidth usage bump up to near 100% for
> short periods and they had satisfactory performance.
>
> I am using VB6 with ADO.
>
> Using the SQL Server from a local network connection gives satisfactory
> performance.
>
> The amount of data transferred is really quite low, so the slow
> performance is a mystery.
>
>
> What could be creating this bottleneck in SQL Server?
>

24 Sep 2006 3:16 PM

Jochen.Markert

You may also want to have the network group check router settings. We
have had problems with packet sizes and routers set incorrectly with
the rest of the network.

25 Sep 2006 11:18 PM

JXStern

On 22 Sep 2006 13:11:24 -0700, jh***@datalyzer.com wrote:

>I have a customer that is operating over a WAN on a T1 line. They are
>experiencing unsatisfactory performance. (they have tried using ODBC
>and OLE-DB connection) They gave the application 30% of the bandwidth a
>put a monitor on it. It never used more than 10% of the available
>bandwidth. They switched to an Access db with the same program doing
>the same queries and saw the bandwidth usage bump up to near 100% for
>short periods and they had satisfactory performance.

Very curious.

Access often moves tons more data than SQLServer to do even simple
stuff, so its bandwidth use is probably misleading.

Is this two-tier with bound controls, or three-tier with a COM server
somewhere?

J.

Thread options