Re: Weekly slowdown

#1

Hi

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?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike (AT) epprecht (DOT) net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"DaveK" <DaveK (AT) discussions (DOT) microsoft.com> wrote

Quote:

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?

#2

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:

http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring

--
Andrew J. Kelly SQL MVP

"DaveK" <DaveK (AT) discussions (DOT) microsoft.com> wrote

Quote:

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?

#3

If 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" <DaveK (AT) discussions (DOT) microsoft.com> wrote

Quote:

Thanks 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:

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:

http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon
counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring

--
Andrew J. Kelly SQL MVP

"DaveK" <DaveK (AT) discussions (DOT) microsoft.com> wrote in message
news:29244FCF-4A99-4ACA-95B4-173B43D001C8 (AT) microsoft (DOT) com...
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?

#4

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

"DaveK" <DaveK (AT) discussions (DOT) microsoft.com> wrote

Quote:

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.

DaveK

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Weekly slowdown

microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering

Re: Weekly slowdown - 04-13-2005 , 04:16 PM

Re: Weekly slowdown - 04-13-2005 , 06:26 PM

Re: Weekly slowdown - 04-14-2005 , 09:23 AM

Re: Weekly slowdown - 04-14-2005 , 02:06 PM