Performance Degradation on SQL 2000

Hello all,
I am wondering why performance dramatically decreases on our SQL Server 2000
the longer we go from rebooting the server.
We are using an ACCESS 2000 custom application as the front end to our SQL
database. (We're in the process of moving that app to .NET). Our server is
running on Windows 2000 Advanced Server, has 2 processors, 4 GB memory, RAID
5 array, plenty of space on the drives. The database size is only
approximately 7GB, Full Recovery model with appropriate backups.
Still, after we reboot the server, everything is running fast again... for a
few days. We had multiple complaints from users in the last few days about
performance slowing down and rebooted the server. It had only been 19 days
from the last reboot.
I've been running profiler and perf mon traces and have spent the last month
doing much optimization, which did significantly help. Yet, we still have
this degradation.
Should the server have to be rebooted frequently? Right now we've decided
to schedule it for once a week, but it just seems that we should not have to
be doing it that often.
Any ideas are most welcome.
Thanks,
DeborahDeborah,
You might want to say what SP and/or patch level of SQL2000 you are on and
also the same for the OS. This helps people who might answer your question.
Chris Wood
Alberta Department of Energy
CANADA
"Deborah Bohannon" < dbohannon@.nationalDONOTSENDHEREcareanetw
ork.com> wrote
in message news:%23TWLkLFCEHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I am wondering why performance dramatically decreases on our SQL Server
2000
> the longer we go from rebooting the server.
> We are using an ACCESS 2000 custom application as the front end to our SQL
> database. (We're in the process of moving that app to .NET). Our server
is
> running on Windows 2000 Advanced Server, has 2 processors, 4 GB memory,
RAID
> 5 array, plenty of space on the drives. The database size is only
> approximately 7GB, Full Recovery model with appropriate backups.
> Still, after we reboot the server, everything is running fast again... for
a
> few days. We had multiple complaints from users in the last few days
about
> performance slowing down and rebooted the server. It had only been 19
days
> from the last reboot.
> I've been running profiler and perf mon traces and have spent the last
month
> doing much optimization, which did significantly help. Yet, we still have
> this degradation.
> Should the server have to be rebooted frequently? Right now we've decided
> to schedule it for once a week, but it just seems that we should not have
to
> be doing it that often.
> Any ideas are most welcome.
> Thanks,
> Deborah
>|||Oops, sorry about that. SQL 2K sp3a; Windows 2K, sp4.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23YLkRVFCEHA.1220@.TK2MSFTNGP10.phx.gbl...
> Deborah,
> You might want to say what SP and/or patch level of SQL2000 you are on and
> also the same for the OS. This helps people who might answer your
question.
> Chris Wood
> Alberta Department of Energy
> CANADA
> "Deborah Bohannon" < dbohannon@.nationalDONOTSENDHEREcareanetw
ork.com> wrote
> in message news:%23TWLkLFCEHA.2796@.TK2MSFTNGP09.phx.gbl...
> 2000
SQL
server
> is
> RAID
for
> a
> about
> days
> month
have
decided
have
> to
>|||No, you shouldn't have to reboot an SQL Server machine. I'd start checking
for memory leaks. Remember that any process or driver can leak memory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Deborah Bohannon" < dbohannon@.nationalDONOTSENDHEREcareanetw
ork.com> wrote
in message news:%23TWLkLFCEHA.2796@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I am wondering why performance dramatically decreases on our SQL Server
2000
> the longer we go from rebooting the server.
> We are using an ACCESS 2000 custom application as the front end to our SQL
> database. (We're in the process of moving that app to .NET). Our server
is
> running on Windows 2000 Advanced Server, has 2 processors, 4 GB memory,
RAID
> 5 array, plenty of space on the drives. The database size is only
> approximately 7GB, Full Recovery model with appropriate backups.
> Still, after we reboot the server, everything is running fast again... for
a
> few days. We had multiple complaints from users in the last few days
about
> performance slowing down and rebooted the server. It had only been 19
days
> from the last reboot.
> I've been running profiler and perf mon traces and have spent the last
month
> doing much optimization, which did significantly help. Yet, we still have
> this degradation.
> Should the server have to be rebooted frequently? Right now we've decided
> to schedule it for once a week, but it just seems that we should not have
to
> be doing it that often.
> Any ideas are most welcome.
> Thanks,
> Deborah
>|||How do you define degragation ? What measure(s) are you using ?|||I will check with our sys admin to see which counters he is still
monitoring. What should I be looking for in checking for memory leaks? Is
there a defined set of criteria?
Thanks,
Deborah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhcY%232OCEHA.1140@.TK2MSFTNGP10.phx.gbl...
> No, you shouldn't have to reboot an SQL Server machine. I'd start checking
> for memory leaks. Remember that any process or driver can leak memory.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Deborah Bohannon" < dbohannon@.nationalDONOTSENDHEREcareanetw
ork.com> wrote
> in message news:%23TWLkLFCEHA.2796@.TK2MSFTNGP09.phx.gbl...
> 2000
SQL
server
> is
> RAID
for
> a
> about
> days
> month
have
decided
have
> to
>|||I define this degradation by the speed at which the users can do the
functions that they do everyday. When using their application (Access 2000
front end), on good days, it takes less than a second to close a form and
move to the next, compared to taking as long as 3 minutes when it is slow; I
get a pretty good barometer about how performance is going.
I have run profiler traces to look at each individual query that is being
executed and used the results as a basis for optimization.
I have run the index tuning wizard to fine tune the indexes.
We use the database in the same way every day. We have the same users every
day.
When productivity comes to a screeching halt, and we start having to pay our
data entry people overtime, management starts getting very vocal about
"fixing" the problem... now.
If I could nail down the problem, a lot of people could start getting more
sleep at night :-)
"Cqlboy" <anonymous@.discussions.microsoft.com> wrote in message
news:1C2BEB63-597B-4B16-8E7E-822C0E2771E5@.microsoft.com...
> How do you define degragation ? What measure(s) are you using ?|||I'd probably start with Perf mon, Process, and check memory for each
process. It might be a good idea to see if there are any particular
recommendations at www.sql-server-performance.com first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Deborah Bohannon" < dbohannon@.nationalDONOTSENDHEREcareanetw
ork.com> wrote
in message news:OF$d5NqCEHA.3568@.tk2msftngp13.phx.gbl...
> I will check with our sys admin to see which counters he is still
> monitoring. What should I be looking for in checking for memory leaks?
Is
> there a defined set of criteria?
> Thanks,
> Deborah
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uhcY%232OCEHA.1140@.TK2MSFTNGP10.phx.gbl...
checking
wrote
Server
> SQL
> server
memory,
> for
> have
> decided
> have
>|||Ok, sound good to me. Are you experiencing time outs ? blocking ? During
times when things are slow have you checked the performance tab of task mana
ger and found the cpu @. 100 % ? Do you see table scans ? What happens when
you run these long queries
within the query analyser ? What net library are you using ? Are connectio
ns using integrated security or sql server auth exclusively ?
Sorry, I guess it looks like the "?" is my favorite. Hope I've stirred some
ideas. Processes going from seconds to minutes can't happen without leavin
g some sort of trace.
-Blake|||All good questions!
During times when things are slow, task manager shows no particular hardship
on the processor. It stays at around 30-40%. Usually, within QA, the
queries are much faster than with the front application, but this is not
always the case. Usually, I am not seeing a lot of blocks from sp_who2
either, though occasionally I do. Used to get timeouts, but then we
increased the timeout. Not seeing tablescans. Users are using integrated
security. They logon to SQL through the application and the application
checks the windows user's permissions internally to determine what level of
access they get to the app. Server settings use the default network
libraries with Named Pipes and TCPIP enabled.
I have to be out of the office the rest of the week, but I am going to have
the sysadmin people run performance monitor while I'm gone and check it on
Monday.
"cqlboy" <anonymous@.discussions.microsoft.com> wrote in message
news:A0F4793C-47C4-4929-9C80-36FE5A40553F@.microsoft.com...
> Ok, sound good to me. Are you experiencing time outs ? blocking ?
During times when things are slow have you checked the performance tab of
task manager and found the cpu @. 100 % ? Do you see table scans ? What
happens when you run these long queries within the query analyser ? What
net library are you using ? Are connections using integrated security or
sql server auth exclusively ?
> Sorry, I guess it looks like the "?" is my favorite. Hope I've stirred
some ideas. Processes going from seconds to minutes can't happen without
leaving some sort of trace.
> -Blake