ODBC Timeout problems but very hard to pin down

[teddysnips]

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).

APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)

All connect to the same database server - different databases, natch.

1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

APP 1 - ACCESS APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. Open a Search form and the default recordset
is displayed correctly.
NOTE: This SQL captured on trace.

2. Enter a criterion into one of the controls on the search form and
press "Filter". Wait a minute and watch the hourglass. Eventually
ODBC Timeout error displayed.
NOTE: This SQL captured on trace.

3. Right-click on the appropriate column on the sub-form and enter
the same criterion as for step 2 above. NO ODBC TIMEOUT - CORRECT
RECORDSET RETURNED WITHIN TWO SECONDS
NOTE: This SQL captured on trace.

NOTE: If I open the (linked) tables in the Microsoft Access database
pane I can manipulate the data at will. However, if I try to open any
stored queries in Access they time out.

APP 2 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display for the
logged-on user contains no records.
2. Change the parameters to allow some records to be displayed.
Watch the progress bar do nothing and then the system displays the
"Object reference not set to an instance of an object" exception.
NOTE: This SQL captured on trace.

APP 3 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display contains
no records.
2. Attempt to add a new record. System pretty slow.
3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

If the above wasn't weird enough for you, get this.

If I cut and paste the SQL that's being captured by the Profiler at
each point and paste it into Query Analyser, it totally barfs. I
waited 11 minutes for one query but it just kept running, never
returning. EVEN THE QUERY AT APP 1 POINT 3.

Needless to say, if I run these queries back at the office it all goes
according to plan.

I think they've got a problem.....

Edward

 

[Mark Rae [MVP]]

I think they've got a problem.....

Yes indeed - they're using ODBC...

Why...?

 

[Ross Culver]

Tuesdays are Microsoft update days. Did your server(s) perform any
automatic Microsoft updates?
Ross

 

[Erland Sommarskog]

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

They always say that.

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).

APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)

All connect to the same database server - different databases, natch.

1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

As long as you only talk about the first two applications, the symptom
is not unknown. Keep in mind that the optimizer builds the query plans
based on estimates from statistics sampled about the data. There are
two different ways that this can go wrong:

1) The plans falls out of cache for some reason; someone runs a huge query,
or the server is restared. The queries are such that they ask for rows
added the last few days, but statistics have not been updated to reflect
this, so the optimizer thinks there are very rows when in fact there are
very many, and picks a bad plan.

2) Statistics are auto-updated, and the plans are recompiled in reaction
to this. The new data causes the optimizer to pick a new plan, which
unfortunately is not as the good as the old one.

The first of these two cases are easiest to address: make sure that
statistics for the updated tables are up to date. The second case may
require tweaking the query and possible adding an index hint.

But from what happens here:

3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

It appears that someone has loaded a lot of data. Of course, this can affect
both performance and results.

I get a little nervous when you say that you have each item four times in
the lookup tables. Doesn't these tables have a primary key?

It could be that they have a corruption and you could run DBCC CHECKDB to
find out. But I'm quite willing to bet that the corruption they have is
on application level. That is, someone has loaded data he shouldn't have
loaded.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[sloan]

ODBC? What year is it?

If you have Access and/or Sql Server, there's not really a good reason to
use ODBC.

...

Change to the other providers, and heck, it'll start working faster, and
you'll be a hero.

 

[teddysnips]

ODBC? What year is it?

Er, the year I wrote the application?

If you have Access and/or Sql Server, there's not really a good reason to
use ODBC.

..

Change to the other providers, and heck, it'll start working faster, and
you'll be a hero.

And you're going to pay me for the rewrite, yes?

Edward

 

[teddysnips]

They always say that.

I know, especially these guys. But what can you do? I asked if I
could install a web cam in the server room, but for some reason they
declined ;¬}

As long as you only talk about the first two applications, the symptom
is not unknown. Keep in mind that the optimizer builds the query plans
based on estimates from statistics sampled about the data. There are
two different ways that this can go wrong:

1) The plans falls out of cache for some reason; someone runs a huge query,
or the server is restared. The queries are such that they ask for rows
added the last few days, but statistics have not been updated to reflect
this, so the optimizer thinks there are very rows when in fact there are
very many, and picks a bad plan.

2) Statistics are auto-updated, and the plans are recompiled in reaction
to this. The new data causes the optimizer to pick a new plan, which
unfortunately is not as the good as the old one.

The first of these two cases are easiest to address: make sure that
statistics for the updated tables are up to date. The second case may
require tweaking the query and possible adding an index hint.

The server has been restarted - it was one of the first things the
sysadmin did when the problem presented. However, as implied, the
problem was there before the reboot. According to what they say,
there was no unusual activity that caused the original problem.

UPDATE: I had a telephone call yesterday that the problem with APP 2
(ASP.NET) had resolved itself (I had done nothing). I then checked
APP 1 (Microsoft Access) and that seemed to have been resolved as
well, though the main user of that system had left for the day. I had
an e-mail this morning from him to say that the problem had recurred.

I think the problem of APP 3 (duplicated data) is most likely a user
error - someone did a bad thing. Yes, there's a primary key, but it's
just a GUID (no, I didn't design the database)

HOWEVER, if I were to single out one question from this sorry tale
that baffles me it would be this.

In the MS Access application, when the Search form is loaded, it
displays a grid (in Access terms, a subform) of returned data.
Because there are only a hundred or so records, it shows all the data
in the table. Yes, bad application design, so shoot me.

You can filter this data by selecting a column, right-clicking and the
context menu exposes a "Filter For: text box. Enter a value, and the
grid/subform is filtered at once.

The SQL trace captures something like
"SELECT Column1, Column2 FROM MyTable WHERE Column2 = "This Value"

This Search form also has a "Filter" button and some controls that
correspond to the columns displayed in the grid. This allows the user
to filter on more than one column. (For any Access people reading
this, in the VB the SELECT string is built up, and a stored query or
QueryDef is edited with the new SQL. Finally, the Data Source
property of the subform is set to the QueryDef). However, if I mimic
the filter from the context menu (the filter, you recall, that WORKED
instantaneously), the application hangs and eventually times out.

The SQL trace captures something like
"SELECT Column1, Column2 FROM MyTable WHERE Column2 = "This Value"

So, the two operations generate identical SQL, but one works exactly
as expected while the other barfs.

FINALLY, and this is where I think the ODBC that the other respondee
here (sloan) alluded to is a red herring, is that if I copy this SQL
to Query Analyzer and run it, it NEVER runs to completion. So there
is no network, or other transport that might affect the result.

My thinking currently, given that the system mysteriously righted
itself yesterday afternoon, only to fall over again this morning, is
that the instance of SQL Server may be corrupted.

Thoughts?

Edward

 

[Erland Sommarskog]

You can filter this data by selecting a column, right-clicking and the
context menu exposes a "Filter For: text box. Enter a value, and the
grid/subform is filtered at once.

The SQL trace captures something like
"SELECT Column1, Column2 FROM MyTable WHERE Column2 = "This Value"

This Search form also has a "Filter" button and some controls that
correspond to the columns displayed in the grid. This allows the user
to filter on more than one column. (For any Access people reading
this, in the VB the SELECT string is built up, and a stored query or
QueryDef is edited with the new SQL. Finally, the Data Source
property of the subform is set to the QueryDef). However, if I mimic
the filter from the context menu (the filter, you recall, that WORKED
instantaneously), the application hangs and eventually times out.

The SQL trace captures something like
"SELECT Column1, Column2 FROM MyTable WHERE Column2 = "This Value"

So, the two operations generate identical SQL, but one works exactly
as expected while the other barfs.

FINALLY, and this is where I think the ODBC that the other respondee
here (sloan) alluded to is a red herring, is that if I copy this SQL
to Query Analyzer and run it, it NEVER runs to completion. So there
is no network, or other transport that might affect the result.

My thinking currently, given that the system mysteriously righted
itself yesterday afternoon, only to fall over again this morning, is
that the instance of SQL Server may be corrupted.

Thoughts?

Trying to diagnose a system on hearsay is about impossible.

But if the query does not complete in QA, maybe should investigate if there
is any blocking.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx