May 2, 2002 at 9:59 am
V 7.0 Service Pack 2
As time goes on the database performance starts degrading. I would consider this system to be OLTP. Rebuilding the indexes helps very little. The problem is not associated w/ locking, tempdb, or any of the transaction logs. There are some Jobs in the scheduler that run throughout the day. And performance will degrade during non peak hours as well. The answer to remedy the problem right now is to restart SQL Server about once every 24 hours.
I have noticed excessive paging at times and that SQL Server for the first couple of hours after a restart will grab about 900MB of Physical memory and as time moves forward it will not generally grab more than 300MB of memory. I have tried setting the Wroking Set Size and this did not help.
My gut feel is that it may be a memory leak and I do not know how to check for this. Any help w/ checking memory leaks and any ideas about the problem are solicited...Please Advice 🙂
Jeff
"Keep Your Stick On the Ice" ..Red Green
May 2, 2002 at 10:09 am
First off, I know that there are some known memory leak problems in certain circumstances with sql server that may be fixed in sp3 or 4(I have not applied 4 yet)
You may want to increase the amount of RAM in your server if you are maxing out(remember that certain combinations of OS and sql only support up to 2 gig, see this article for details:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q274750)
Someone on this site suggested UPDATING STATISTICS manually to resolve a problem I was having with slow inserts. I found that doing this with the FULL SCAN option set on my largest tables that undergo the most inserts/updates resolved my problem.
Of course there are many variables that can be the root of performance degradation problems(poor queries, too many indexes) so these suggestions may not get to the root of the problem.
May 2, 2002 at 10:19 am
The application is poorly tuned. I just walked into this shop and we will get to things like excessive indexes.
I WANT SQL Server to hold onto the memory. For some reason it doesn't get it back. It runs great when it is holding 800 to 900 MB and it slows when all it can get is about 200 to 300 MB which happens some time after reboot(awful solution 🙂
There are only about 10 users for the system. Concurrently, I would say 3 to 4 and rarely do they query at the same time. Replication is also running on the Server.
Service Pack 3 is not an option at this time.
Thanks for the posts...Keep em' going !!
"Keep Your Stick On the Ice" ..Red Green
May 2, 2002 at 10:29 am
1 Gig of RAM.
"Keep Your Stick On the Ice" ..Red Green
May 2, 2002 at 10:35 am
You can dynamically configure minimum RAM in server properties. Is that what you meant by working set size?
May 2, 2002 at 10:37 am
I would agree that another gig is warrented.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply