|
|
Posted by Erland Sommarskog on 08/16/07 14:27
(teddysnips@hotmail.com) writes:
> 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, esquel@sommarskog.se
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
Navigation:
[Reply to this message]
|