Database Slow Connects Using Sniffer on a network - SQL 6.5.416 
Author Message
 Database Slow Connects Using Sniffer on a network - SQL 6.5.416

Hello,

Background information:
I'm using Windows NT 4.0 Server (sp 5) to host SQL Server 6.5.416 (thats
6.5
sp5a). On my LAN, in the same domain, I am using an NT Workstation 4.0
with a
SQL client to connect to this server. The LAN is connected via a CAT 5
cable,
from a central switch. The only protocol used by the server is TCP/IP.
DNS is
enabled, though LMhosts is not enabled. There are 2 servers defined in
the DNS
service search order and 1 in the domain search. The network adapter
details are:
Netelligent 10/100 TX, with UTP slot 7, bus 2. The connection speed is
100 MBps
in the full duplex mode. The driver version is 4.26 with TLAN version at
3.0.

Situation/Issue Details:
We used a sniffer to tap into the network at this workstation. We did a
network
capture by first launching an application which uses SQL calls within
it, and it gave us "Database Slow Connect" messages, which averaged
190ms. When we did a
second network capture by only connecting via the SQL client (just an sa
login), it
gave us the similar "Database Slow Connect" messages, averaging 155ms.
The sniffer messages gave us some explanation for the errors, that
either the server
was too busy or the threshold for slow connects is set to too low (in
this case, to 30
ms which was a default value). What I would like to know is, if these
values we
are seeing are within the acceptable range or not. Note that I tried
this to
connect with two different SQL servers within the same domain, on the
same LAN,
with similar results. This was done during business hours with an
average of about 5
users connected but "awaiting command" when I did an 'sp_who' for the
servers.

Thank you for your time and patience.
Hope for quick and speedy responses.


Best regards,
Rajesh Tuliani



Sun, 03 Aug 2003 23:19:31 GMT
 Database Slow Connects Using Sniffer on a network - SQL 6.5.416

Rajesh - do you actually have a problem or are you just worried about the
messages that the sniffer wizard is giving you?  If you have a problem then
ignore the wizard and look at the actual packet trace itself.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq



Mon, 04 Aug 2003 16:50:38 GMT
 Database Slow Connects Using Sniffer on a network - SQL 6.5.416
Neil,

My customers are experiencing an unusual delay in opening up their application,
and we were investigating the cause of this problem.
Note that if we ping across the network, it is reached in less than 10ms,
however a simple SQL login (to port 1433) takes 155ms.
If we run the application, the login via the application takes an average of
190ms!

What I want to find out is if this delay is reasonable over this high speed
local network, since the sniffer has a default database connect value of 30ms.

I couldn't find anything on the MSDN/Techet sites. Would appreciate your
guidance.

Thanks,
Rajesh

Quote:

> Rajesh - do you actually have a problem or are you just worried about the
> messages that the sniffer wizard is giving you?  If you have a problem then
> ignore the wizard and look at the actual packet trace itself.

>  Neil Pike MVP/MCSE.  Protech Computing Ltd
>  Reply here - no email
>  SQL FAQ (484 entries) see
>  http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
>  (faqxxx.zip in lib 7)
>  or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>  or www.sqlserverfaq.com
>  or www.mssqlserver.com/faq



Mon, 04 Aug 2003 21:55:21 GMT
 Database Slow Connects Using Sniffer on a network - SQL 6.5.416
 Your users are noticing a delat of .155 seconds?  Doesn't seem that long to
me....

 How much memory is there on the server?  There was an NT memory fragmentation
bug with SQL logons that I got on an NT 4.0 SP4 server - upgrading to 6a fixed it
 - but that was causing delays of over a second and the app repeatedly logged on
(like 6 times for one "screen") and so was very noticeable.

 > My customers are experiencing an unusual delay in opening up their
application,

Quote:
> and we were investigating the cause of this problem.
> Note that if we ping across the network, it is reached in less than 10ms,
> however a simple SQL login (to port 1433) takes 155ms.
> If we run the application, the login via the application takes an average of
> 190ms!

> What I want to find out is if this delay is reasonable over this high speed
> local network, since the sniffer has a default database connect value of 30ms.

> I couldn't find anything on the MSDN/Techet sites. Would appreciate your
> guidance.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq


Tue, 05 Aug 2003 03:10:02 GMT
 Database Slow Connects Using Sniffer on a network - SQL 6.5.416
Neil,

The application makes several calls to the SQL database, with multiple logins
(similar to what you described). The window does get frozen and we have done data
integrity checks and index rebuilds, along with regular DBCC maintenance - so we have
ruled out data integrity. The application source code is unknown to us - but it could
be another reason, but we need to rule out this issue of database slow connects.
The server has about 512MB of RAM, which seems to have hit its physical limit for
expansion purpose. We are looking into getting a new box but that would take us some
time.

I did read the release notes for SP6a but couldn't find anything on memory
fragmentation - is it fixed possibly in an earlier version? Please let me know. As I
mentioned, we already have sp 5 on the server.

Thanks,
Rajesh

Quote:

>  Your users are noticing a delat of .155 seconds?  Doesn't seem that long to
> me....

>  How much memory is there on the server?  There was an NT memory fragmentation
> bug with SQL logons that I got on an NT 4.0 SP4 server - upgrading to 6a fixed it
>  - but that was causing delays of over a second and the app repeatedly logged on
> (like 6 times for one "screen") and so was very noticeable.

>  > My customers are experiencing an unusual delay in opening up their
> application,
> > and we were investigating the cause of this problem.
> > Note that if we ping across the network, it is reached in less than 10ms,
> > however a simple SQL login (to port 1433) takes 155ms.
> > If we run the application, the login via the application takes an average of
> > 190ms!

> > What I want to find out is if this delay is reasonable over this high speed
> > local network, since the sniffer has a default database connect value of 30ms.

> > I couldn't find anything on the MSDN/Techet sites. Would appreciate your
> > guidance.

>  Neil Pike MVP/MCSE.  Protech Computing Ltd
>  Reply here - no email
>  SQL FAQ (484 entries) see
>  http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
>  (faqxxx.zip in lib 7)
>  or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>  or www.sqlserverfaq.com
>  or www.mssqlserver.com/faq



Tue, 05 Aug 2003 07:44:56 GMT
 Database Slow Connects Using Sniffer on a network - SQL 6.5.416
 Rajesh - it was an undocumented fix and was either in SP5 or 6a - we went straight to
6a and that fixed it.  Also do a network trace at the server end and see if it's having
to do domain lookups to authenticate them or anything.

 > The application makes several calls to the SQL database, with multiple logins

Quote:
> (similar to what you described). The window does get frozen and we have done data
> integrity checks and index rebuilds, along with regular DBCC maintenance - so we have
> ruled out data integrity. The application source code is unknown to us - but it could
> be another reason, but we need to rule out this issue of database slow connects.
> The server has about 512MB of RAM, which seems to have hit its physical limit for
> expansion purpose. We are looking into getting a new box but that would take us some
> time.

> I did read the release notes for SP6a but couldn't find anything on memory
> fragmentation - is it fixed possibly in an earlier version? Please let me know. As I
> mentioned, we already have sp 5 on the server.

> Thanks,
> Rajesh


> >  Your users are noticing a delat of .155 seconds?  Doesn't seem that long to
> > me....

> >  How much memory is there on the server?  There was an NT memory fragmentation
> > bug with SQL logons that I got on an NT 4.0 SP4 server - upgrading to 6a fixed it
> >  - but that was causing delays of over a second and the app repeatedly logged on
> > (like 6 times for one "screen") and so was very noticeable.

> >  > My customers are experiencing an unusual delay in opening up their
> > application,
> > > and we were investigating the cause of this problem.
> > > Note that if we ping across the network, it is reached in less than 10ms,
> > > however a simple SQL login (to port 1433) takes 155ms.
> > > If we run the application, the login via the application takes an average of
> > > 190ms!

> > > What I want to find out is if this delay is reasonable over this high speed
> > > local network, since the sniffer has a default database connect value of 30ms.

> > > I couldn't find anything on the MSDN/Techet sites. Would appreciate your
> > > guidance.

> >  Neil Pike MVP/MCSE.  Protech Computing Ltd
> >  Reply here - no email
> >  SQL FAQ (484 entries) see
> >  http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> >  (faqxxx.zip in lib 7)
> >  or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> >  or www.sqlserverfaq.com
> >  or www.mssqlserver.com/faq

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq


Wed, 06 Aug 2003 01:12:11 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Database Slow Connects Using Sniffer on a network - SQL 6.5.416

2. Database Slow Connects Using Sniffer on a network - SQL 6.5.416

3. Database Slow Connects Using Sniffer on a network - SQL 6.5.416

4. Help: How to connect to an Access database over a network using VB3

5. Connecting to a database using dial-up networking

6. The question: connect to SQL-server using a Dial-Up networking profile

7. Connect to SQL Server using a Dial-Up networking profile

8. Connect to SQL-Server using a Dial-Up networking profile

9. Connect to SQL-server using a Dial-Up networking profile

10. Connect to SQL server using a Dial-Up networking profile

11. Very slow to connect the client over the network

12. Unable top Connect to Local SQL Server 6.5 database - keeps brining up dial up networking


 
Powered by phpBB® Forum Software