Performance degrading over time

From: Tim (Tim_at_NOSpam.com)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 15:50:06 -0500

Proliant DL360 G3 w/hyper threading

2 physical CPU, 2 gigs of memory

MSA 1000

Windows 2003

SQL 2000 SP3a

Active/Passive failover clustering

We are noticing the performance worsens over time in our SQL cluster. The
performance happens across different stored procedures, so it is not singled
out to one. A stored procedure after initial compilation takes 200 ms to
run but after three or so days the same SP takes 450 ms to run. The only
way to get our performance back to 200ms is to failover the cluster. I know
over time the performance should increase due to data and procedure cache
being reused. Has anyone else run into this issue? Any inputs on what to
look for. CPU, memory, physical disk i/o and network utilization are all at
a minimum.

Relevant Pages

  • Re: So, anyone asked about Win64 at Borcon?
    ... Since scalability and large memory access ... CLR stored procedures. ... will be slightly faster in extended stored procedures than CLR stored ... procedures for each processor type you want to run your SQL server on. ...
    (borland.public.delphi.non-technical)
  • Re: Table variables
    ... > I am working with a system that uses quite a lot of stored procedures ... > database server quickly rises to close to 2 Gb. ... The amount of memory ... > used by sql server is limted but as soon as the memory reaches the ...
    (microsoft.public.sqlserver.server)
  • Table variables
    ... I am working with a system that uses quite a lot of stored procedures ... database server quickly rises to close to 2 Gb. ... The amount of memory ... Is there any way of de-allocating the memory ...
    (microsoft.public.sqlserver.server)
  • Re: Clustered Indexes
    ... If you use up most of thememory for the procedure cache you will take memory ... How much memory do you have and what settings do you have for Sql Servers ... > tables,poorly coded stored procedures) SQL is using it up in the cache? ...
    (microsoft.public.sqlserver.server)
Loading