|
|
Posted by Marco van de Voort on 05/22/06 16:10
Hi,
does sb recognize (aspects of) the following problem? Or better, know a
solution or direction to search?
At work I've inherited a series of delphi applications that access a common
database using SQL Server 2000 (sp3, sp4 update in preparation due to this
problem). Applications run on one server, db on the second. Both are dual
xeon 2.8 or 3 GHz with 2 GB ram. The apps use about one GB (800MB) memory,
the db too (is configured to use more, 1.8GB, but doesn't.) The db is also
replicated to a third machine.
The problem is that sometimes, after a cascade of query timeouts (recorded
by the apps in the eventlog, cause is the commandtime set on all
components), the whole applications seems to stop responding. Restarting the
apps doesn't solve the problem, rebooting the application server does, which
leads me to believe the problem is in MDAC on the app server? The app server
has an own unused sql server instance (used in migrations) btw.
The problems occur during busier times, but nothing spectacular (up to
ten-thousand of queries per hour maybe).
The problem sometimes goes away after a few minutes in about half of the
cases, but if not, it seems perpetual till reboot (at least 13 hours).
Another notable point is that not all queries time out, most writes (which
append a row or change a row) seem to go ok, same with selects that get a
record for a primary key value, and pure read selects flagged with NOLOCK.
The queries that go wrong all get lists that touch central tables (either
directly or via joins).
The behaviour is consistent with an external row/page lock somewhere that
doesn't go away.
Database layout is fairly uninteresting. A db or 3 (one read-only), the
larger one having say 30 tables,
cardinality of the tables is not that much of a problem. Tens of thousands
of rows max, except a logging table with maybe 300000 tuples. (which is only
traversed for mgmnt info, and not during busy hours) No binary or other
disproportionally large fields, Most db access done based on primary/foreign
keys.
Other details:
- Replication overhead can be considered low (we are talking about
thousand(s) mutationsper day, nothing significant.
- commandtimeout on all db components is set (to 30s)
- all cursors are clientside, except the component used for getting lists,
that has
location=cluseserver; cursortype=ctopenforwardonly;
cachesize=250; locktype=readonly
- the apps are not threaded.
- D6 patched with all three patches
Thnks in advance
Navigation:
[Reply to this message]
|