December 11, 2001 at 6:41 am
We're running SQL 7.0 and everyday we have to Stop then Restart SQL because the Memory usage keeps going up without every giving any of it back. It usually is a slow steady climb. By the end of the day it's running so slow that we have to restart the Program. We have 1Gig of RAM in the Server also. I see that SQL will take most of the RAM. Also that Enterprise Manager has been know to cause leaks. But we only run that to make changes. Any help would be great. Thanks
December 11, 2001 at 6:43 am
Have you manually configured the 'minimum memory' setting on the sql server (properties, memory tab - i think)
Steven
December 11, 2001 at 6:58 am
These are the Memory settings we have currently.
Dynamically Configured is checked with a Minimum of 0 and a Maximum of 639 (out of 1023)
Fixed Memory is not checked (511 appears greyed out)
Reserved Physicla Memory for SQL is not checked and the Minimum Query memory that appears at the bottom is set to 1024.
We have played around with these settings alittle but nothing seemed to help.
December 11, 2001 at 7:02 am
One question I would ask would be what jobs are running throughout the day that could potentially cause memory munching? I have seen Mail tasks eat memory quite efficiently and from what I have seen, once SQL takes it, it really doesn't like to give it back. Not uncommon for SQL to eat 1 Gig.
Additionally, how many connections are supported simultaneously? Is that eating most of the memory as well?
When you say you have to restart it, is it because there are problems or just that you see memory peaking?
I guess, it would be good to have a better understanding of what the box is doing from the database perspective, how much memory is actually in use and what the activity is on the box. That will help in any further diagnosis.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 11, 2001 at 7:10 am
You could run a profiler and correlate this with the increate in RAM useage from Performance Monitor and this might clarify the issue. When I discovered this site I had an identical problem and had a long-running thread on this until it was solved. The correlation showed that SHRINKDATABASE leaked my SQL memory and caused an incredible slowdown of the server. This is a recognised issue, and it is worthwhile checking that this database option (autoshrink databases) is not enabled. Hope this is your issue, as it is easy to fix. If not, the logging may throw up something useful.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 11, 2001 at 7:10 am
Have you checked 'task manager' to see what process is eating the memory
Steven
December 11, 2001 at 8:06 am
Within Task Manager sqlserv.exe is what takes up most of the memory.
There is alot of Querying of the data going on. Most of the people doing the querying are using Access with and ODBC driver. I believe the reason for this is to make it more user friendly. From what I'm told the SQL Query function is alittle difficult to grasp for basic users. We have noticed that occasionally when someone starts a Query that actually multiple request will show up under "In Use" and "Users" on the Server Manager for this Server. Could there be run away Queries?
December 11, 2001 at 8:56 am
SQL Server does not provide a way to throttle queries, so once a query is begun, it will use all the resources it needs to complete. This can cause problems with CPU, but I haven't really seen memory run away, though I usually have had enough memory on my recent db servers to not notice.
I think David has a good idea iwth jobs. Perhaps there is some job that is eating up memory? Or perhaps the Access connections are using "Connected recordsets" and are holding memory?
Check that, also check locks.
Can you limit the Access queries for an hour or so and watch memory? See if it rises.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 12, 2001 at 9:49 am
Doug's Question:
Pardon my ignorance, but I'm new to SQL. Could you tell me how I can run a
> Profiler to compare with the Performance Monitor. I'm trying to get the
> bonus points for fiqureing out this problem.
Paul Ibison said
To run the profiler, use Start/Programs/Sql Server/Profiler. The events and data columns measured are best looked at in a book or in BOL (books on line). If the volume of transactions is not overwhelming you might want to trace most variables. Admittedly this is a slight drain on resources but if your server is in such a bad state, this may save time solving the problem.
Use the second tool - performance monitor, which is above the Profiler in the same menu, and look at:
Total Server Memory
Buffer Cache Hit Ratio
% Processor Time
(again, check in BOL, or a SQL Admin book)
Now for the fun bit: when these monitored variables change for the worse, stop both the profiler and monitor then try to correlate the profile with the SQL trace. This can take a while if there is a lot going on, but in my case it showed a relationship I would have otherwise missed.
December 12, 2001 at 10:00 am
You will have to use the timestamps to correlate them. You are trying to match an event in Profiler with the worsening or changing performance in Performance Monitor.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 12, 2001 at 10:50 am
If you're concerned about mem usage, why not change to fixed mode? Im not sure that what is happening is bad or wrong - natural for SQL to take memory to cache both data and execution plans, will hold it until OS needs more than it has.
Andy
December 12, 2001 at 11:31 am
How can I put time stamps into Performance Monitor and Profiler?
December 12, 2001 at 12:53 pm
Perf does this automatically. In profiler, there is a data column for starttime.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply