Re: Occasional delay in processing incoming packets

From: David G. (david_nospam_at_nospam.com)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 01:26:44 -0400

James wrote:
> I have a problem where occasionally (less that 1%) a packet is not
> processed for as long as 25 seconds.
>
> 1) I get the exact same behavior with ADO 2.8 and ADO.NET.
>
> 2) A detailed view of the chain of events looks like this:
>
> . T+0 seconds - connection from the pool is allocated
> . T+0 seconds - vb call to Command.Execute (or c# call to
> DataAdapter.Fill)
> . T+0 seconds - single packet (118 bytes) is sent to database machine
> (from a web server)
> . T+0.1 seconds - ack received from database machine indicating packet
> was successfully received in full
> . T+25 seconds - SQL Profiler trace "Start Time" for database call
> . T+25 seconds - SQL Profiler trace "End Time" for database call
> . T+25 seconds - data returns to application
> . T+25 seconds - connection is returned to the pool
>
>
> So the "lost time" occurs on the database machine between the time the
> packet is received, and the time Profiler claims SQL Server started
> working on the query.
>
> I've tried to run perfmon on the obvious counters on the database but
> I've failed to find anything out of the ordinary. There are so many
> counters! Any ideas on what specifically I should be looking at now?
>
> ~ James
>
> Occasional delay in processing incoming packets

If you can, try setting up an alias to teh SQL Server machine using a
different network protocol (e.g. named pipes instead of TCP/IP). That
way you can see if the problem is related to the network library. If the
start time in Profiler is T+25, it sounds like SQL Server is not getting
the packet until T+25.

You can set up aliases from the SQL Server Client Tools - Client Network
Utility. 

-- 
David G.

Relevant Pages

  • Re: Changing Query behavior based on local vs. remote context?
    ... We had a business network and a process control network that had to be splittable for political reasons. ... They negotiated large frame sizes since they were both on FDDI, not considering that the equipment in the middle couldn't pass that big a packet. ... Microsoft SQL Server MVP ... do not do packet splitting can cause this. ...
    (microsoft.public.sqlserver.security)
  • Re: Is the SA login secure
    ... --this is an example of a captured password packet ... Jasper Smith (SQL Server MVP) ... > When I login with an sa account to a SQL server on our network how is the ...
    (microsoft.public.sqlserver.security)
  • ODBC Connection with SQL Managed Provider
    ... on a "network drive". ... database using the Microsoft .NET Framework 1.1 Data Provider for SQL ... authorizing the user against a table in the SQL server database. ... for the app to display the "splash" screen (which is the first thing it does ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: SQL Server 2005 64 bit on Window Server 2003 R2 64bit slow per
    ... even gone as far as swapping network cables and updating NIC card drivers to ... If the box itself checked out fine, then move on to runnign SQL Server on ... Perfmon, Disk I/O, Pages, Profiler traces, checked locks/blocks, long running ... slow on these boxes to me. ...
    (microsoft.public.sqlserver.setup)
  • Odd connection problem (proxy + internal)
    ... system and can not be placed facing the internet. ... that belongs to our network and also connects to this accounting ... DSN's) to our internal SQL server. ... by installing the Winsock proxy on the SQL server and mapping the ip ...
    (comp.databases.ms-sqlserver)