Weekly slowdown

Weekly slowdown

Post by RGF2ZU » Fri, 15 Apr 2005 04:10:02

Our main app running on active/passive cluster, SQL2K SP3a, Win2K SP4 seems
to work fine for about a week at a time, with response times in the 2 second
or less range. Then, suddenly, they go up to between 4 and 20 seconds and
stay that way. No new processes running, not any real growth in data files,
nothing I can pin it on. Switching resources to the (formerly) passive
server seems to fix the problem for another few days or week, then we need to
go through it all over again. Anyone have any ideas what to look at? The
boxes are Dell 4 CPU, 4GB RAM connected to Dell/EMC SAN. Due to nature of
app, there is little actual growth of space used, but users are very
sensitive to slowdowns, since it is a data entry app. Three tier app,
mid-tier has no changes. What should I get performance stats on or what
Profiler settings should I look at to identify what is happening?

Weekly slowdown

Post by SQL M » Fri, 15 Apr 2005 06:16:09


Rebuilt indexes around the time before the slowdown happens? Do you rebuild
indexes at all?

How much RAM is allocated to SQL Server? Is the /3gb switch set in boot.ini?

Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland


MVP Program: http://www.yqcomputer.com/

Blog: http://www.yqcomputer.com/


Weekly slowdown

Post by Andrew J. » Fri, 15 Apr 2005 08:26:56

It could be lots of things but my first guess would be memory issues. Do
you have any other apps running on this box other than sql server? Do you
do a lot of adhoc queries? Maybe these will help:

Performance WP's
Hardware Performance CheckList
SQL 2000 Performance tuning tips
Disk Monitoring

Andrew J. Kelly SQL MVP

Weekly slowdown

Post by RGF2ZU » Fri, 15 Apr 2005 22:31:04

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:


Weekly slowdown

Post by RGF2ZU » Fri, 15 Apr 2005 22:33:05

Also, no other apps on the box besides SQL, but I guess with the recompiles
that is essentially ad hoc queries. There are not lots of reports summary
lookups done. We extract to another DB on another box for our reports.

Weekly slowdown

Post by Andrew J. » Fri, 15 Apr 2005 23:23:09

f you haven't done reindexing in a while it is probably worth doing if not
manually every so often. 2K reads and 200 - 500 MS is not good in my
opinion but I have no idea what they are doing. If you have proper indexes
it should only be a few ms and a few reads. If the reads stay the same but
the time goes up it can be due to several things. One is blocking, excessive
CPU use, disk I/O vs Logical I/O or maybe recompiles. I would concentrate
on the sp's that are run the most often and see if you can optimize them or
the underlying tables (add indexes etc) to get the reads down. That should
reduce all the other issues associated with it. You also want to find out
why the sp's are getting recompiled all the time. They should reuse the
existing plans and not do a recompile.

Andrew J. Kelly SQL MVP

"DaveK" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


Weekly slowdown

Post by RGF2ZU » Sat, 16 Apr 2005 02:59:04

I looked at the "extent scan fragmentation" on the largest tables using
showcontig. Mostly they are running in the 90% or above range, although when
there is also
"logical scan fragmentation" reported, that is often in the 6% range.
Should these
indexes be dropped and rebulit or reindexed with dbreindex? DBCC
indexdefrag did not seem to do much for performance.


Weekly slowdown

Post by Andrew J. » Sat, 16 Apr 2005 04:06:30

Removing fragmentation is a good thing to do in general anyway. If you used
Indexdefrag the statistics will not be updated so you want to run
sp_updatestats at some point. If you run DBCC DBREINDEX it will update the
stats automatically after the rebuilding of the index but is an offline
operation. If you have multiple files in the filegroup Extent fragmentation
is not accurate.

Andrew J. Kelly SQL MVP