ODBC call failed - weird behavior

Hello,
I have been working on updating an Access 2000 application that uses linked
ODBC tables to connect to an SQL Server 2000. My development machine is
connected to the server over a Microsoft LAN, at 100Mbits.
Both the Office 2000 on my development machine and the SQL server has all
the latest SP's
Everything worked fine for months until today. I am not aware of any changes
to ether machine since yesterday.
Now, there is one table that can not be opened any more.
I can access all the other tables ecxept that one. When I try to open it I
get the message "ODBC call failed". The table is displayed but with #Name?
in all columns for all records.
Once that has happened I get the same message for all tables. In addition to
"ODBC call failed" there is also
"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
When I close down and reopen the app I can again access all tables except
the bad one.
At first, I thought the table had gotten corrupted, but I can open the table
just fine on the server. If I set up an ODBC on the server with the same
name I and run my application there. the problem table opens fine.
I removed the ODBC and set it up again with the same properties, client uses
TCP/IP network library, and I am using SQL Server authentication.
I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
I ran
I am wondering if I should update to 2.8
If anyone has a suggestion I would appreciate it very much.
RagnarIt could be an MDAC issue. You could run the MDAC component
checker on a machine where the app works and another machine
where the app gets the ODBC error and see if there are any
differences in MDAC versions. You can get component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
You could also turn on ODBC tracing then reproduce your
error and then turn tracing off again. You could open up the
ODBC trace log, look for specifics of what's failing and try
to track down the issue from there.
-Sue
On Wed, 10 Mar 2004 19:05:29 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:

>Hello,
>I have been working on updating an Access 2000 application that uses linked
>ODBC tables to connect to an SQL Server 2000. My development machine is
>connected to the server over a Microsoft LAN, at 100Mbits.
>Both the Office 2000 on my development machine and the SQL server has all
>the latest SP's
>Everything worked fine for months until today. I am not aware of any change
s
>to ether machine since yesterday.
>Now, there is one table that can not be opened any more.
>I can access all the other tables ecxept that one. When I try to open it I
>get the message "ODBC call failed". The table is displayed but with #Name?
>in all columns for all records.
>Once that has happened I get the same message for all tables. In addition t
o
>"ODBC call failed" there is also
>"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
>When I close down and reopen the app I can again access all tables except
>the bad one.
>At first, I thought the table had gotten corrupted, but I can open the tabl
e
>just fine on the server. If I set up an ODBC on the server with the same
>name I and run my application there. the problem table opens fine.
>I removed the ODBC and set it up again with the same properties, client use
s
>TCP/IP network library, and I am using SQL Server authentication.
>I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
>I ran
>I am wondering if I should update to 2.8
>If anyone has a suggestion I would appreciate it very much.
>Ragnar
>|||Thank you Sue,
I had run ComponentChecker on my development machine and it now has 2.6 SP2
Refresh wih a couple of files being slightly newer than expected, ODBC32.dll
and ODBCCP32.dll.
The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
expected, including the ODBC32.dll and ODBCCP32.dll.
I thought I had run a trace, but I did not realize I had to switch it on. I
ran a trace and found these error lines toward the end. This trace was only
running while I opened the problem table in the DB window.
MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 09022400
DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionRead (recv()). (10054)
DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sock
ets]General
network error. Check your network documentation. (11)
I am not familiar with ODBC calls, but I see there were two error messages
I have done some more testing and found that I can open a form that displays
the contents of this table in a listbox with no errors. The RowSource for
the listbox is a select query that selects all fields of the table.
Yet, if I create a stand alone query that does the same, it fails.
I am beginning to suspect network problems, possibly the NIC on the server.
We had problems with that before and it was replaced. The connection into
the main network from my machine also goes through a switch, which could be
acting up.
I still think I am going to update MDAC to the same as the server, or do you
think 2.8 would be better?
Ragnar|||You'd definitely want to be on 2.7 SP1 at least due to all
of the bug fixes for it since 2.6 SP2. I'd try to go that
route first being that it works on your server.
2.8 has a lot of security changes and it should be tested in
another environment if possible before installing. It's the
base mdac version for Windows 2003 but I don't think
anything other products, service packs, etc are installing
it at this point.
The list box testing you did is odd, otherwise I'd suspect
network issues as well (or wouldn't rule them out anyway).
Were both of the connections, data source properties for the
list box and the stand alone query exactly the same?
-Sue
On Thu, 11 Mar 2004 13:13:12 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:

>Thank you Sue,
>I had run ComponentChecker on my development machine and it now has 2.6 SP2
>Refresh wih a couple of files being slightly newer than expected, ODBC32.dl
l
>and ODBCCP32.dll.
>The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
>expected, including the ODBC32.dll and ODBCCP32.dll.
>I thought I had run a trace, but I did not realize I had to switch it on. I
>ran a trace and found these error lines toward the end. This trace was only
>running while I opened the problem table in the DB window.
>MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
> HSTMT 09022400
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionRead (recv()). (10054)
> DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP S
ockets]General
>network error. Check your network documentation. (11)
>I am not familiar with ODBC calls, but I see there were two error messages
>I have done some more testing and found that I can open a form that display
s
>the contents of this table in a listbox with no errors. The RowSource for
>the listbox is a select query that selects all fields of the table.
>Yet, if I create a stand alone query that does the same, it fails.
>I am beginning to suspect network problems, possibly the NIC on the server.
>We had problems with that before and it was replaced. The connection into
>the main network from my machine also goes through a switch, which could be
>acting up.
>I still think I am going to update MDAC to the same as the server, or do yo
u
>think 2.8 would be better?
>Ragnar
>|||Thank you Sue,

> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.

> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar|||Hi, I had a similar issue with an Oracle database connection. I got around
it by removing the linked tables, compacting/repairing the Access database a
nd recreating the links. I suspect that Access is maintaining some informat
ion about the back-end tables that is not visible and is corrupt. If so, th
is information is removed by the repair if the link is gone. Anyway, hopefu
lly this will also work for you.
... Bob

quote:

---

Originally posted by Ragnar Midtskogen
Thank you Sue,

> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.

> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar

---

Occasional delay in processing incoming packets

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 packetsJames 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.|||I created a Named Pipes alias and used that instead of TCP/IP. I've
never done that before, so I checked to make sure the connections were
using Named Pipes before continuing.
During this test I still have the same problem.
"David G." <david_nospam@.nospam.com> wrote in message news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> 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.|||James wrote:
> I created a Named Pipes alias and used that instead of TCP/IP. I've
> never done that before, so I checked to make sure the connections were
> using Named Pipes before continuing.
> During this test I still have the same problem.
> "David G." <david_nospam@.nospam.com> wrote in message
> news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
>> 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.
Is this occurring from only one client or from all clients? What SP are
you running on SQL Server?
--
David G.|||There are 11 web servers and they all exhibit the same behavior.
At first I thought the times of the occurrences on the different web
servers did not correlate ? but now I think they do. Previously I was
only tracking delays of 5 seconds or greater. I think if I track all
delays I may get more matches.
I also tried tracing everything that was executing on the server ? I
did that for 6 minutes and I had one delay of 6 seconds on one web
server to compare. Then I looked for things that started at the same
time that my delayed sp was supposed to start? and I found only two
items ? which is very strange because this is a busy database. So I
grouped the results of the trace by StartTime per second yielding 360
numbers . The normal parts of the graph bounce around between 200 and
600 (not including full text calls) ? and for the second that I
submitted the delayed sp, the number plummets to 2. Full text calls
plummet to 0, and EndTimes for this second are 0 also. This was
followed by a peak of 860 (non-ft StartTimes) 7 seconds later. I do
not know if this happens every time ? I have more testing to do.
I wish I had somewhere I could post the data for DL but I do not right
now.
Anyone know what the capacity of SQL server is for incoming sql
requests? It averaged 895/second (including fulltext index calls to
sp_fulltext_getdata) with a peak of 1549 during this 6 minutes.
>> What SP are you running on SQL Server?
Service Pack?
SQL Server Machine:
Windows Server 2003 Enterprise (NT 5.2 (3790))
Quad Xeon 3.06GHz, 4GB RAM
SQL Server Enterprise 8.00.818 (SP3)
Stored Procedure?
The stored procedure I targeted just does a single row select on a
small table using the PK. I haven't gone to this level of detail on
any other sp, but I assume the problem can affect any call to the db.
~ James
"David G." <david_nospam@.nospam.com> wrote in message news:<eDkzArfhEHA.3912@.TK2MSFTNGP11.phx.gbl>...
> James wrote:
> > I created a Named Pipes alias and used that instead of TCP/IP. I've
> > never done that before, so I checked to make sure the connections were
> > using Named Pipes before continuing.
> >
> > During this test I still have the same problem.
> >
> > "David G." <david_nospam@.nospam.com> wrote in message
> > news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> >> 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.
> Is this occurring from only one client or from all clients? What SP are
> you running on SQL Server?

Occasional delay in processing incoming packets

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 packetsJames 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.|||I created a Named Pipes alias and used that instead of TCP/IP. I've
never done that before, so I checked to make sure the connections were
using Named Pipes before continuing.
During this test I still have the same problem.
"David G." <david_nospam@.nospam.com> wrote in message news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.ph
x.gbl>...
> James wrote:
> 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.|||James wrote:[vbcol=seagreen]
> I created a Named Pipes alias and used that instead of TCP/IP. I've
> never done that before, so I checked to make sure the connections were
> using Named Pipes before continuing.
> During this test I still have the same problem.
> "David G." <david_nospam@.nospam.com> wrote in message
> news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
Is this occurring from only one client or from all clients? What SP are
you running on SQL Server?
David G.|||There are 11 web servers and they all exhibit the same behavior.
At first I thought the times of the occurrences on the different web
servers did not correlate but now I think they do. Previously I was
only tracking delays of 5 seconds or greater. I think if I track all
delays I may get more matches.
I also tried tracing everything that was executing on the server I
did that for 6 minutes and I had one delay of 6 seconds on one web
server to compare. Then I looked for things that started at the same
time that my delayed sp was supposed to start and I found only two
items which is very strange because this is a busy database. So I
grouped the results of the trace by StartTime per second yielding 360
numbers . The normal parts of the graph bounce around between 200 and
600 (not including full text calls) and for the second that I
submitted the delayed sp, the number plummets to 2. Full text calls
plummet to 0, and EndTimes for this second are 0 also. This was
followed by a peak of 860 (non-ft StartTimes) 7 seconds later. I do
not know if this happens every time I have more testing to do.
I wish I had somewhere I could post the data for DL but I do not right
now.
Anyone know what the capacity of SQL server is for incoming sql
requests? It averaged 895/second (including fulltext index calls to
sp_fulltext_getdata) with a peak of 1549 during this 6 minutes.

Service Pack?
SQL Server Machine:
Windows Server 2003 Enterprise (NT 5.2 (3790))
Quad Xeon 3.06GHz, 4GB RAM
SQL Server Enterprise 8.00.818 (SP3)
Stored Procedure?
The stored procedure I targeted just does a single row select on a
small table using the PK. I haven't gone to this level of detail on
any other sp, but I assume the problem can affect any call to the db.
~ James
"David G." <david_nospam@.nospam.com> wrote in message news:<eDkzArfhEHA.3912@.TK2MSFTNGP11.ph
x.gbl>...[vbcol=seagreen]
> James wrote:
> Is this occurring from only one client or from all clients? What SP are
> you running on SQL Server?

Occasional delay in processing incoming packets

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
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.
|||I created a Named Pipes alias and used that instead of TCP/IP. I've
never done that before, so I checked to make sure the connections were
using Named Pipes before continuing.
During this test I still have the same problem.
"David G." <david_nospam@.nospam.com> wrote in message news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> James wrote:
> 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.
|||James wrote:[vbcol=seagreen]
> I created a Named Pipes alias and used that instead of TCP/IP. I've
> never done that before, so I checked to make sure the connections were
> using Named Pipes before continuing.
> During this test I still have the same problem.
> "David G." <david_nospam@.nospam.com> wrote in message
> news:<eOaWzPOhEHA.1276@.TK2MSFTNGP09.phx.gbl>...
Is this occurring from only one client or from all clients? What SP are
you running on SQL Server?
David G.
|||There are 11 web servers and they all exhibit the same behavior.
At first I thought the times of the occurrences on the different web
servers did not correlate but now I think they do. Previously I was
only tracking delays of 5 seconds or greater. I think if I track all
delays I may get more matches.
I also tried tracing everything that was executing on the server I
did that for 6 minutes and I had one delay of 6 seconds on one web
server to compare. Then I looked for things that started at the same
time that my delayed sp was supposed to start and I found only two
items which is very strange because this is a busy database. So I
grouped the results of the trace by StartTime per second yielding 360
numbers . The normal parts of the graph bounce around between 200 and
600 (not including full text calls) and for the second that I
submitted the delayed sp, the number plummets to 2. Full text calls
plummet to 0, and EndTimes for this second are 0 also. This was
followed by a peak of 860 (non-ft StartTimes) 7 seconds later. I do
not know if this happens every time I have more testing to do.
I wish I had somewhere I could post the data for DL but I do not right
now.
Anyone know what the capacity of SQL server is for incoming sql
requests? It averaged 895/second (including fulltext index calls to
sp_fulltext_getdata) with a peak of 1549 during this 6 minutes.
[vbcol=seagreen]
Service Pack?
SQL Server Machine:
Windows Server 2003 Enterprise (NT 5.2 (3790))
Quad Xeon 3.06GHz, 4GB RAM
SQL Server Enterprise 8.00.818 (SP3)
Stored Procedure?
The stored procedure I targeted just does a single row select on a
small table using the PK. I haven't gone to this level of detail on
any other sp, but I assume the problem can affect any call to the db.
~ James
"David G." <david_nospam@.nospam.com> wrote in message news:<eDkzArfhEHA.3912@.TK2MSFTNGP11.phx.gbl>...
> James wrote:
> Is this occurring from only one client or from all clients? What SP are
> you running on SQL Server?