hanks to both for your suggestions - I have some homework to do on the
tuning tips, but here is what I have found so far -
Server has 4GB ram, Win2K Advanced Server, no /3GB switch in boot.ini.
Ram allocated is dynamic up to max of 3968mb. We do not have any particular
index rebuild schedule on any of the tables.
In profiler I notice that some common queries recompile each time they are
run, which are many times a day. That seemed odd to me. The basic app is an
order entry system, with about 20-30 simultaneous users. Every few minutes
orders are uploaded to a mainframe for further processing. Database growth
is slow as older records are deleted once they have been uploaded. Even the
user login stored proc recompiles each time it is run.
The whole DB is 4GB in size, some important tables have 500-600K records,
one has 3M history records.
The most common queries do about 2K reads and execute in 200 to 500
milliseconds, which seems good to me. When the system slows down, the reads
are the same, but the time expended goes up. Log backups are done every 3
hours, full backup once a day.
I get the feeling that I am not looking at the correct items to find the
problem. Although I can see the evidence for the problem, I don't see where
it is coming from. This DB has a total of 75 tables, 380 stored procs, and
was developed by at least 10 different programmers over 4 years. I use
Profiler to select queries that take over 200 ms. to execute and that
recompile. Is there other items I should look at?
Several of the links Andrew suggested are broken, the first two and the one
to swynk don't work.
"Andrew J. Kelly" wrote: