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.

Do you have any monitoring software in place? Sounds like a memory leak.
Anything else running on the boxes?

Cheers,

Rod

MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog

"Tim" <Tim (AT) NOSpam (DOT) com> wrote

No monitoring software installed. Just on occasion perfmon and profiler.
We do have a lot of DTS packages that are scheduled to run via sql agent.
We also use Com+ and MSMQ.

"Rodney R. Fournier [MVP]" <rod (AT) die (DOT) spam.die.nw-america.com> wrote in
message news:#AdXL5JsEHA.2008 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

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 (AT) NOSpam (DOT) com> wrote

Hi Tim,

From your descriptions, I understood that you stored procedures will take
more time to execute until you have you SQL Server restarted. Have I
understood you? Correct me if I was wrong.

Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional.

Please be advised that contacting phone support will be a charged call.
However, if you are simply requesting a hotfix be sent to you and no other
support then charges are usually refunded or waived.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

For now, I would like to provide a useful document on how to narrow down
the performance issue with profiler tracing and analysis

HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587/en-us

Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Mingqing Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

First off, thanks everyone for their replys.

Mingqing, you are somewhat correct, what we do to fix our problem is
failover the cluster not restart SQL Server. Was told it was not
recommended to stop SQL on a cluster but was better to fail it over.

Tim


""Mingqing Cheng [MSFT]"" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Hi Tim,

Oops, it's my mistyping, what I mean is failover the cluster.

Is there anything I could do to help you regarding this issue, feel free to
let me know your questions or concerns

Sincerely yours,

Mingqing Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!