Re: Performance degrading over time

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 10/13/04 

Date: Tue, 12 Oct 2004 20:06:39 -0400

If you have lots of other things than SQL Server on the same box you might
consider setting the MAX memory config in SQL Server to an amount that will
leave enough memory for them to operate properly minus about 200MB. 

-- 
Andrew J. Kelly  SQL MVP
"Tim" <Tim@NOSpam.com> wrote in message 
news:eDgsO0JsEHA.536@TK2MSFTNGP11.phx.gbl...
> 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: Locking pages in memory
    ... the account SQL runs under) the permission to lock pages in memory. ... Server Standard, Enterprise, and Developer editions: Required for SQL Server ...
    (microsoft.public.sqlserver.setup)
  • Re: Worker Threads
    ... In order to use more than 4GB of memory you must use AWE. ... One is you must have Enterprise Edition of SQL ... set in the Boot.ini and AWE enabled in SQL Server. ...
    (microsoft.public.sqlserver.setup)
  • Re: Inexplicable slowdowns
    ... I profiled the SQL operation and it is taking no time at all. ... I also noticed that the server was operating at the end of available physical memory and as a result of perfmon monitoring, I saw that these slowdowns occur when the available physical memory dips to 100mb or so. ... I think SQL server is the culprit. ... It's set to consume a max of 3GB. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: basic question
    ... Currently we have are doing calculations via stored procedures ... in which case *I* would like to have them run on the SQL server ... procs are doing. ...
    (microsoft.public.dotnet.general)
  • Re: Frage zu View-Tabellen
    ... eigenes Element in SQL. ... Erweiterungen sind indizierte Sichten (SQL Server 2000), ... Performance und vom Speicherverbrauch ein Unterschied, ob ich eine Anfrage auf eine View-Tabelle oder eine Stored Procedure schicke? ... Bei Stored Procedures wird der kompilierte Ausführungsplan im Cache gespeichert, der bei Zugriffen auf die View in der Regel neu erstellt wird. ...
    (microsoft.public.de.sqlserver)