Very Computer

I am a C++ developer working a large, multithreaded server product
using SQL Server 7.0 (SP2).  Our server (a computer Telephony server)
makes heavy heavy use SQL Server from multiple threads using ODBC.
I'm having some terrible performance problems with accessing SQL
Server ODBC that only happen on a Dual CPU box.  I'm wondering if
someone can help me figure out what's going on.

Our Telephony runs exclusively on Windows 2000/NT (server or
workstation), the same computer that SQL Server runs on.   Until now
we have all developed and tested on single CPU boxes.  Recently we
received a batch of Dual Dell Optiplex boxes for development and
testing.   (Dual PIII 933 MHz, 256 MB RAM).

I was stunned to find that the server ran horrendously more slowly on
the Duals than it did on the singles.  Initialization on a single CPU
box takes a matter of roughtly 10-20 seconds.  On a dual box it takes
over a minute.

The problem initially occurs in acquiring ODBC connections.  The first
couple of connections (SQLDriverConnect calls) go pretty quickly, but
eventually a thread will lock up inside of SQLDriverConnect and not
return for at least 10 seconds.

When SQLDriverConnect returns (with failure) the error message is
invariably one of the following errors:

[Microsoft] [ODBC SQL Server Driver][Shared Memory] General Network
Error.  Check your network documentation

            OR

[Microsoft] [ODBC SQL Server Driver] Timeout expire

And then on the existing connections we DO have (never more than 10 at
once) we start getting horrendous performance from queries.  The same
queries that took milliseconds start taking 15 seconds or more (though
they all eventually succeed).

So far I've tried a number of remedies:

1. I've tried enabling/disabling ODBC connection pooling to no effect.
I've tried implementing my own connection pooling (caching HDBCs) to
very little effect (I get a little further before a connection
attempts start slowing down).

2. I've ensured that SQL Server allows an unlimited number of
connections.

3. I've tried setting the DSN network protocol to both named pipes and
TCP/IP -- no effect

4. I tried setting SQL Server to use only one of the CPUs -- no effect

5. I tried running SQL Server Profiler.  The delay occurs between the
time we make the ODBC function call and the time that SQL Server
receives it.  Once SQL Server gets the requrest it seems to process it
instantaneously.  I don't know if this is accurate or just what
Proiler is reporting

SQL Server driver version:    2000.80.194.00
ODBC32.DLL version:            3.520.427687936

Thanks,

Joe O'

This has a bad smell, because in 7.0 RTM the Shared Memory NetLib is not
enable on Windows NT, only on Windows 9x.
In SQL Server 2000 it is enabled, you did not install any SQL Server 2000
related pieces did you?

In 7.0 do not use Shared Memory, all host side benchmarks on 7.0 I have seen
and done do not use Shared Memory, but TCP/IP sockets, either by forcing it
through the connect string, using an alias or using a (system) DSN.

-GertD

I am almost certain that this problem is because you are using the
SQL2000-driver on SQL7. I once installed SQL2000 on one machine (which ran
fast), but connections to another machine with SQL7 were very slow. Every
connection setup seemed to take about 3 seconds. Deinstalling SQL2000 and
installing SQL7 solved the problem for me at that time.

Gert-Jan

I think you and Giovanni are right about the SQL Server 2000 thing.
When I was setting up this box, the first thing I did was
(accidentally) install SQL Server 2000.  When I realized my mistake I
uninstalled it.  Apparently the uninstall did not remove the SQL
Server 2000 driver.

Now I've installed SQL Server 7.0 but its driver does not seem to have
overwritten SQL Server 2000s.

Is there any way to manually uninstall the SQL Server 2000 pieces?

Joe

p.s. FYI - At startup I am creating approximately twenty connections.
Most of them I hold on to but there were a couple that were
continually being recycled.  After five or so, they start bogging down
as I've described.

Quote:> How many connections in total and how many are you creating per
second or
> minute.

> This has a bad smell, because in 7.0 RTM the Shared Memory NetLib is
not
> enable on Windows NT, only on Windows 9x.
> In SQL Server 2000 it is enabled, you did not install any SQL Server
2000
> related pieces did you?

> In 7.0 do not use Shared Memory, all host side benchmarks on 7.0 I
have seen
> and done do not use Shared Memory, but TCP/IP sockets, either by
forcing it
> through the connect string, using an alias or using a (system) DSN.

> -GertD

Thanks (to you and to Gert-Jan Strik) for responding

I think you are both right about the SQL Server 2000 thing.  When I
was setting up this box, the first thing I did was (accidentally)
install SQL Server 2000.  When I realized my mistake I uninstalled it.
Apparently the uninstall did not remove the SQL Server 2000 driver.

Now I've installed SQL Server 7.0 but apparently its driver has not
overwritten the stray SQL Server 2000 driver that was left over.
That's my guess anyway

Is there any way to manually uninstall the SQL Server 2000 pieces
without breaking my SQL Server 7 installation??   If I *must*
completely uninstall SQL Server 7, how can I be sure that I've
completely removed the SQL Server driver?

Or is there perhaps some way to turn off the "Shared Memory" setting?

Joe

p.s. FYI - At startup I am creating approximately twenty connections.
Most of them I hold on to for the entire run, but there were a couple
that were continually being recycled.  After five or so, they start
bogging down as I've described.

Quote:> How many connections in total and how many are you creating per
second or
> minute.

> This has a bad smell, because in 7.0 RTM the Shared Memory NetLib is
not
> enable on Windows NT, only on Windows 9x.
> In SQL Server 2000 it is enabled, you did not install any SQL Server
2000
> related pieces did you?

> In 7.0 do not use Shared Memory, all host side benchmarks on 7.0 I
have seen
> and done do not use Shared Memory, but TCP/IP sockets, either by
forcing it
> through the connect string, using an alias or using a (system) DSN.

> -GertD

I just downloaded MDAC 2.5 SP1 and replaced my versions of
SQLSRV32.DLL and ODBCBCP.DLL as you suggested and the problem was
solved.

Gert, I cannot possibly thank you enough.  I have spent over a week
trying to fix this problem, optimizing database code, fiddling with
driver settings, etc.    I was about to ditch this dual-cpu box.  Yet
in 3 lines of text you solved it for me.

Thank you!

Joe O'Leary

Quote:> Kill SQLSVR32.DLL and ODBCBCP.DLL and reinstall MDAC should do the
trick, or
> if you do not want to reinstall use a connect string overwriting the
used
> Net Library or use an alias or DSN to connect to.
> -GertD